Nov 15, 2017 7:13:00 PM by Clarisa Maman Orfali
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:
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:
Each toolbar can be easily customized through the option Customize.
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.
We open a new Editor Window and enter the following PL/SQL code:
Cursor c1 IS SELECT ename
WHERE deptno = 20;
FETCH c1 INTO ename;
EXIT WHEN c1%NOTFOUND;
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:
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!
How to get the most out of Toad for Oracle
Most developers and DBAs use Toad for Oracle to reduce time and effort to develop and manage Oracle databases. But did you know that with Toad for Oracle you can automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation? Did you know Toad can now find and control sensitive data across all your Oracle databases? What else can Toad do that you didn’t know about? Which edition will benefit you the most?
Tags: Toad for Oracle
Written by Clarisa Maman Orfali
Clarisa is from Argentina. She is Founder and Director at ClarTech Solutions, Inc. a company dedicated to the development, consultancy and training in Information Technologies located in Southern California in the United States.
She is a Systems Engineer with more than 18 years of experience in systems development. She was introduced in Oracle Technologies in 2009, a few years later she discovered the great power of development with Oracle Application Express (APEX) and has specialized since then in that area.
She is also Co-Founder of the Oracle Users Group of Argentina (AROUG) and founder of Oracle APEX Latin America meetup, has also actively participated in the organization and as Presenter of big and popular events such as the OTN Tour and the Oracle APEX Tour of Latin America. In 2014 was recognized by the Oracle Corporation with the Oracle ACE Award. Then, in March 2018, she was promoted to Oracle ACE Director.
She is also the author of the first three books in Spanish about Oracle APEX: