Generate Oracle SQL Statements Easily with KeepTool’s Clipboard Features

Updated Oktober 2025

The Visual Query Builder

Oracle SQL generation statements is among the most frequent tasks Oracle developers face. In this newsletter, we’ll explore several methods that help developers complete this work much more efficiently.

When joining multiple tables, you can use KeepTool’s Query Builder, accessible in any editor window via the construction-site icon.

Hora’s Visual SQL Query Builder

For example, double-click the LOCATIONS table in the left navigation bar to bring it into the designer on the right. Then, right-click in the designer and select a related detail table such as DEPARTMENTS. The Query Builder will connect them using a RIGHT OUTER JOIN, since the foreign key LOCATION_ID in DEPARTMENTS may be null. Use the checkboxes next to column names to choose which columns to display, and make sure to check the “ANSI Joins” box at the bottom. After closing the dialog, you will see the generated SQL in the code window:

   SELECT 
     LOCATIONS.CITY,
     LOCATIONS.POSTAL_CODE,
     DEPARTMENTS.DEPARTMENT_ID,
     DEPARTMENTS.DEPARTMENT_NAME
   FROM LOCATIONS
   RIGHT OUTER JOIN DEPARTMENTS ON (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID)

Copy SQL to Clipboard

There are times when you need a simple SQL statement based on a single table without writing all the column names yourself. In this case, use the Overview → Tables page and select the “Copy to clipboard…” item from the context menu.

SQL Generation with Clipboard Output (CURSOR FOR LOOP, etc.)

Context-aware SQL code generation such as a CUSROR FOR LOOP

This option provides several types of statements you can generate for the selected table:

• SELECT statement

• INSERT statement

• UPDATE statement

• MERGE statement

• DELETE statement

• CURSOR FOR LOOP

• WHERE condition

The final two items generate templates for Oracle’s expdp export and impdp import tools using the tables you selected:

  expdp system@PORA12C1 tables=DEPARTMENTS,LOCATIONS 
   directory=DATA_PUMP_DIR  dumpfile=pora12c1.dmp reuse_dumpfiles=y logfile=pora12c1.log

Show DDL

Another useful command on the Overview → Tables page is “Show DDL”.

Reverse DDL Code Generation for Database Objects

Using our Reverse/DDL engine, it generates the DDL needed to create the selected tables.

You can also find the “Copy to clipboard” and “Show DDL” menu items for other database object types such as Packages, Sequences, and Users.

The Editor Window

When you are in an editor window, additional SQL-generation options become available. Open the DB Object Browser (click the question-mark icon on the toolbar), drag a table name into the code window, double-click it to select the name, then right-click and open the “Selected Object” submenu.

Drag and Drop SQL Code Generation

From there, you can insert the selected object into different types of SQL or PL/SQL, such as:

• SELECT statement

• INSERT statement

• UPDATE statement

• CURSOR FOR LOOP

For example, choosing CURSOR FOR LOOP produces:

  BEGIN
    FOR rec IN (
      SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
      FROM DEPARTMENTS
    ) LOOP
      -- process rec
    END LOOP;
  END;

Quick shortcuts are available for all these commands. For instance, you can expand a table name into a SELECT statement by pressing Ctrl + Shift + S.
This enables you to rapidly start from a generated template and then customize the SQL or add additional statements to the loop.

See How It Works!

Explore how KeepTool supports you in generating SQL quickly and efficiently.