The Toad for Oracle Editor Window

In this article, we will learn how to use the Toad for Oracle Editor Window.

This tool provides us with a simple editor for working with SQL queries, PL/SQL code and more! This editor is the main interface of Toad for Oracle.

Anything we can execute via SQL*Plus we can also execute via Toad’s editor. This includes:

• Anonymous Blocks
• SQL queries
• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• PL/SQL
• SQL*Plus scripts
• RMAN commands
• Stored Java procedures

For this demo, we will open a new connection. We are going to use the SCOTT user to connect to our Oracle database.

Once the connection is open, we can see the editor button in the windows bar above the connection bar.

We can open multiple editor windows using the same connection. For this, we can click with the right mouse button on the SCOTT connection button and select New > Editor.

In this way we can have as many open editor windows as we need with the same connection.

Moreover, we have the possibility to organize the different toolbars provided by the Toad according to our needs, simply selecting or deselecting the option so that the selected toolbar is displayed or not.

The upper toolbar, called “Standard”, gives us access to the editor, the schema browser, the database browser, the sessions browser, and the SQL Builder, which we will see how to work with in another article, as well as many other features!

We also have the following toolbars:

• Windows Bar
• Desktops
• Connection Bar
• Jump Search
• Workspaces
• Intelligence Central
• Toad World

Each toolbar can be easily customized through the option Customize.

Working with the Editor Window

We access the editor window and enter the following SQL query:

Select * from EMP;

And then click on the Execute/compile statement at caret button  to execute the SQL query. This button will execute only the SQL instruction where the cursor is positioned. If we have various SQL queries, we can select each one individually for its execution.

By clicking on the Execute button, the SQL statement that the cursor is on (or the highlighted SQL) is sent to the Oracle database. If the query is successful, then the data will be returned and displayed in the output area in the Data Grid tab.

The Execute as script button   will execute all the contents of the editor window as if we were using a SQL*Plus interface. There is a separate script output tab for the result of this type of execution.

The Clear button  clears the contents of the editor window. We also have the option to simply open a new tab by right-clicking on “New 1” and selecting the “New Tab” option as well as the type of tab we want to open.

Alternatively, we can click on the +sign next to the existing open tab to open other tabs. Each of these tabs is associated with a single connection to the Oracle database.

This approach makes it easy to execute previous SQL statements, especially when they are being tested. This technique is also very useful for the PL/SQL code when we use it as packages. We can put the specifications of the package in a single tab and the body of the package in another tab so in this way they are kept separate. The body of the package will not be compiled correctly if there is a problem with the specifications.

It is important to note that Toad automatically places the specifications and package body in separate tabs when opening a package from a file or from the database. This function is controlled by View Menu > Toad Options > Editor > Open > Object Loading > Packages/Types, where we can choose this function or disable it.

Now, we access the editor window again and write a second SQL query, as follows:

Select * from dept;

We can execute the SQL statements one by one, clicking on the Execute button, or we can execute them together by clicking on the Execute as script button.

The result of both queries can be seen in the Output tab or alternatively, a "Grid 1" tab for query 1 and a "Grid 2" tab for query 2 are displayed.

Execute PL/SQL Code

We open a new Editor Window and enter the following PL/SQL code:

DECLARE
   ename    VARCHAR2(10);
   Cursor       c1 IS SELECT ename
                       FROM emp
                       WHERE deptno = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO ename;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(ename);
   END LOOP;
END;
/

We execute the code by clicking the Execute as script button.

In the Output Window we can see that the code was executed correctly.

A very powerful feature of this tool is that it provides us with Code Templates. These templates are blocks of PL/SQL code that are incorporated into the tool or that we can have generated as the basis of our own codes.

For the use of code templates incorporated in Toad, we simply write in the editor window the following:

Anon <Ctrl><Spacebar>

The Code Template select list opens and, for example, we can select the first code template that is the Anonymous block template with a double click to activate it in the editor window.

Each template uses a name, a description, and the code component. If we write the name followed by the sequence of keys <Ctrl><Spacebar> (without blank spaces), the template will be activated automatically in the editor window.

If the code inserted in the editor window has errors, the tool will show us a red circle, indicating the error line.

The code templates can be viewed, added, removed, edited, exported, and imported from View Menu > Toad Options > Editor > Code Templates.

There is much more to learn about the Editor Window of Toad for Oracle, so be on the lookout for my upcoming publications!