Oracle 7.3 - Oracle 23c 20+ years of experience Version (Released: )

SQL and the Windows clipboard

13 Mar 2015

The SQL Query Generator

Building SQL statements is among the most common tasks that an Oracle developer is faced with. In this newsletter, we will explore some approaches that let the developer do this in a more efficient manner.

For joining multiple tables, you can use KeepTool’s Query Builder, which you can access from any editor window by clicking the construction-site icon:

Double-click the LOCATIONS table in the left-side navigation bar to bring it into the designer window on the right. Then, by right-clicking in the designer window, use the context menu to add a related detail table such as the DEPARTMENTS table. The Query Builder connects them with a RIGHT OUTER JOIN, since the foreign key LOCATION_ID in DEPARTMENTS may be null. Use the checkboxes next to the column names to select the columns to be displayed. Be sure to also check the “ANSI Joins” box at the bottom. Then click OK to close the dialog and you’ll see the generated query 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 to Clipboard

There will also be cases where you want only a simple SQL statement based on a single table without typing in all the column names. Here, you can use another helpful option. Go to the “Overview Tables” page and open the “Copy to clipboard …” context menu item.

This gives you a choice of several types of SQL or PL/SQL statements that can be generated for the selected table:

  • SELECT statement
  • INSERT statement
  • UPDATE statement
  • MERGE statement
  • DELETE statement
  • CURSOR FOR LOOP
  • WHERE condition

The final two items produce templates for Oracle’s expdp export and impdp import tools using the tables that you may have 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, also accessible from Overview Tables is the “Show DDL” menu item.

Based on our Reverse/DDL engine, it generates the DDL to create the selected tables.

You can find the “Copy to clipboard” and “Show DDL” menu items for various other database objects on pages such as Packages, Sequences and Users.

The Editor Window

Once you are in an editor window, there are other options for SQL generation. Open the DB Object Browser (question mark icon on the toolbar) and drag a table name into the code window. Double-click the name to select it. Then click the right mouse button, and open the “Selected Object” submenu.

This menu lets you insert the selected object name into several types of SQL or PL/SQL, such as

  • SELECT statement
  • INSERT statement
  • UPDATE statement
  • CURSOR FOR LOOP

For example, choosing CURSOR FOR LOOP produces the following code:

begin
  for rec in (
    SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
    FROM DEPARTMENTS
  ) loop
  end loop;
end;

There are quick and easy shortcuts for all these commands. For example, you can expand a table name to a SELECT statement by pressing Ctrl+Shift+S.

In this way, you can easily perform custom modifications to the SQL and add more statements to the loop.

See how it works!

Explore possibilities of KeepTool.

i,g