SQL Queries and PL/SQL with Oracle SQL Developer

There are a few policies that make writing and testing queries and other code more convenient.

Termination characters

When writing several queries into one file each should be terminated with a semicolon. That way, execute command (F9/) and explain plan (F6/) will pick the statement at the cursor position and execute the action only for this statement. For example:

SELECT name, population
FROM Country;

SELECT name, country, province
FROM city
WHERE name LIKE 'Freib%';

However, PL/SQL code has to be terminated by a semicolon, a newline, and a slash to separate it correctly from other statements, e.g.:

CREATE TYPE coastline_table AS TABLE OF VARCHAR2(30);
/

SELECT name, CAST(COLLECT(DISTINCT sea) AS coastline_table) coastlines
FROM Country JOIN geo_sea ON code=country
GROUP BY name;

Executing parts of a statement

By highlighting a part of a statement, an action (execute, explain, run script) will be performed only for the selection. Of course, the chosen part has to be a valid statement—e.g., a query without its WHERE-clause:

SELECT *
FROM Country

WHERE code='de';

Run Script

The command run script (F5/) normally takes the whole input file and executes every statement. Results and (error) messages will be shown in the script output window. By highlighting parts of the file the execution may be restricted to fewer statements. Sometimes it is also useful to run a single query as script in order to display its results in the script output window (plain text) instead of getting the usual result table.

SELECT name, capital
FROM Country;

Comments

Line comments, starting with '--', and block comments, starting with '/*' and ending with '*/', are supported. For example:

/*****************
 * A simple query
 *****************/

SELECT name -- country name, unique
FROM country;

Functions, Procedures, and Types

Unfortunatley, when creating functions, procedures, and types there are no meaningful messages when the source has errors. The SQL*Plus command SHOW ERRORS does not work in SQL Developer. However, the creation process will succeed and the new object can be selected in the connections tree. The code editor (available in the context menu) shows errors and messages after compiling the source (Ctrl-Shift-F9/).

CREATE TYPE coastline_table AS TABLE OF VARCHR2(30);
/

Warning: Type created with compilation errors.

DBMS Output

For testing and debugging PL/SQL code it is helpful to use DBMS_OUTPUT.PUT_LINE() to show messages during the execution. They appear in the DBMS output window if switched on ().

BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
END;
/

anonymous block completed

Explain Plan

Oracle provides the explain-plan-command to inspect the execution plan of a statement. It can be executed by the UI of the SQL Developer (F6/) or in script form. The example shows both ways.

EXPLAIN PLAN
SET STATEMENT_ID='correlation'
FOR
SELECT CORR(population, area) 
FROM Country;

SELECT 
  id, cardinality "ROWS", operation, projection, 
  options, object_name, position "COST/POSITION"
FROM Plan_table 
START WITH id = 0 AND statement_id = 'correlation'
CONNECT BY PRIOR id = parent_id AND statement_id = 'correlation'
ORDER BY level ASC, position ASC;

If the statment id is left out, the plan table has to be empty (DELETE FROM Plan_table). More information on the meaning of the columns can be found in Oracle's PLAN_TABLE reference. The following statement uses a recursive WITH and selects all excution plans:

WITH 
execution_plan(id, plan_id) AS 
  (
    SELECT 0, plan_id 
    FROM Plan_Table
    WHERE id=0
    UNION ALL
    SELECT p.id, p.plan_id
    FROM execution_plan e join Plan_Table p
      ON e.id=parent_id
      AND e.plan_id=p.plan_id
  )
SEARCH DEPTH FIRST BY id SET lvl
SELECT 
  statement_id, plan_id, lvl, id, cardinality "ROWS", operation, 
  projection, options, object_name, position "COST/POSITION"
FROM execution_plan JOIN Plan_Table USING(id, plan_id)
ORDER BY plan_id, lvl ASC, position ASC;