Constructing and Running Oracle SQL Statements

Oracle Execution Engines

Oracle has two “execution engines” to run statements:

• SQL Engine (mid to late 1970’s since inception of Oracle database)
• PL/SQL Engine (added in late 1980’s with Oracle 6 – incidentally the first version of Oracle I dealt with while working in the database support team at Oracle Melbourne)

The type of statement being executed will dictate which execution engine is used. Sometimes Oracle needs to switch from one engine to another while executing a script. This is known as a “context switch”.

SQL Engine

This engine is used to execute plain/vanilla SQL statements. If a SQL statement looks like it can be executed on any type of database (MySQL, SQL Server, etc), then it is likely to be executed by the SQL Engine. Some examples:

SQL statements adhere to the ANSI SQL standard.

PL/SQL Engine

In the 1980’s wanted to extend SQL capabilities to add programming constructs – flow control, exception handling, iteration, etc. So they implemented a whole separate execution engine – Procedural Language for SQL. It is based on the Ada programming language, as that was popular at the time, especially in the realm of the Defense industry (Oracle’s earliest large customers were defense related).

Functions, procedures, packages and triggers are all PL/SQL constructs. Examples of PL/SQL statements:

PL/SQL statements can contain plain SQL statements within them. As the PL/SQL engine executes the statement, if it encounters a SQL statement, it will context switch to the SQL engine and have it execute that statement (and then switch back again)

Statement Terminators

In Oracle, all statements need to be terminated somehow. For SQL statements this is easy, simply end the statement with a semi-colon (;). For example:

UPDATE emp SET deptno = 10 
WHERE emp_name = 'Rod';

UPDATE emp SET deptno = 20
WHERE emp_name = 'Bill';

The terminating semi-colon tells the SQL engine where each statement ends. SQL Statements can span multiple lines like above. Blank lines can be placed between statements, but should be avoided in the middle of a statement. In the example above, 2 separate statements would be executed.

PL/SQL is a bit trickier, as many of the actual “lines” of code need to finish with a semi-colon – it’s part of the PL/SQL language definition. With that being said, every PL/SQL statement needs to finish with: END;

Here is a brief example using an anonymous (fancy term for it not being a stored procedure) PL/SQL block:

DECLARE
    l_char VARCHAR2(1);
BEGIN
    FOR i IN 0 .. 127 LOOP
        l_char := CHR (i);
        INSERT INTO ascii_tab(id, character) VALUES(i, l_char); 
    END LOOP; 
    COMMIT; 
END;

Tools to Run SQL and PL/SQL

There are two basic choices for how you run statements:

• A command line tool – the most obvious choice is Oracle SQL Plus, which has been around from day one and comes with Oracle database and client software.
• A GUI Tool – there are a bunch available, but Oracle provides SQL Developer.

A command line tool is good for scripting and automation. You can build files that contain lots of SQL statements and then have the ability to run the entire file or script easily.

SQL Plus

SQL Plus is an old product that runs from the command line. It will run SQL and PL/SQL statements. It also has its own commands for doing things like formatting column widths from your queries, aggregating results and providing summaries, “page and heading” layout, etc. More about this later.

SQL Developer

This is Oracle’s GUI tool for executing statements. It has some quirks but is generally good to use because it is optimized for the Oracle database. If you have used GUI tools on other databases, you will be familiar with how to use it. To install it, you just download the zip file and unzip it anywhere you like – it does not go through any form of “installer”. On the downside, it can be a bit bloaty and use up quite a lot of memory.

Anything you can do in SQL Plus can be done here. Most of the SQL Plus only commands are also supported by SQL Developer. And you can run scripts also.

Running Statements in SQL Plus

Starting SQL Plus

To start SQL Plus, you just enter “sqlplus” from the command line. It will prompt for a username, password and connection alias/string to connect to a database. Alternatively, you can put all this on the command line. Like this: sqlplus scott/tiger@prod

Once logged in, you will have a SQL Plus command line. It will look like this: SQL>

Running Statements

Running a single plain SQL statement in SQL Plus is straightforward – just type it in (line by line), terminate with a semi-colon and hit enter. The semi-colon terminates the statement and tells SQL Plus to run the statement.

Running a PL/SQL statement is slightly different. You will have “END;” or “end;” to terminate the statement, but on the next line you must enter a forward slash (/) – this tells SQL Plus to run the command.

Once a statement is run, it gets put into a memory “buffer”. There are commands to edit the contents of the buffer, but this is beyond the scope of this article. To rerun the statement in the buffer, you can enter a forward slash again and hit enter.

The concept of the “/” to run commands in the buffer is a SQL Plus concept only.

Formatting

There are some SQL Plus (only) commands that you should know to make query output readable:

• SET PAGES XX – This sets the page size. XX is a numerical value which sets the number of rows that are displayed before the column headings are repeated. If you choose a value of 0, then you will get no column headings at all. Usually I do SET PAGES 1000 to only get headings printed once.
• SET LINESIZE XX – Sets the width of a line (defaults to 80 characters)
• COLUMN <col_name> FORMAT … – This is used to format column widths. For example:
  COLUMN EMP_NAME FORMAT A20 – Format the EMP_NAME column to be 20 characters
  COL EXCHANGE_RATE FOR 99.999999 – Numerical format to display. I used the abbreviated version of the command in this example.

There are lots of other SET commands to various things in SQL Plus, but this is enough to get you by.

Gotchas

By default, there is no “auto-commit” turned on. You must manually commit or rollback. If you logout of SQL Plus (by typing “exit”), by default you will lose uncommitted changes.

SQL Plus commands like SET and COLUMN are not SQL or PL/SQL commands. They are commands that are only understood by SQL Plus. So, these commands must be put “outside” of any SQL or PL/SQL statements (before or after them).

SQL Statements with “&” characters can cause problems. The & character denotes variable substitution in SQL Plus. So if you have a script that has something like:

UPDATE dept SET department_name = 'Murders & Executions' 
WHERE dept_no = 50;

Then you will need to do this:

SET DEFINE OFF
UPDATE dept SET department_name = 'Murders & Executions' 
WHERE dept_no = 50;

Scripts

You enter a bunch of SQL statements into a text file (usually with a .sql extension). You can then run the entire file by just entering:
SQL> @file_name.sql

Running Statements in SQL Developer

Most of the above applies here also. But everything is much easier, as query results are displayed in a nice grid by default, rather than displayed as text. So, most of the time you will not need to format the output.

Commands are entered in a query panel, and the output is displayed in a grid below that. You can pile up multiple statements in the query panel.

It’s good practice to terminate each statement with a semi-colon. By doing this, your cursor can be anywhere “within” a statement and the run button will run just that statement. Alternatively, you can highlight a statement (or a portion of it) and hit the run button.

There is also a “Run Script” button. This will operate more in “SQL Plus mode” and display the output as text. The run script button will run whatever statement is highlighted. If nothing is highlighted it will run everything in the query pane. For convenience, the SQL Plus only commands (SET, COLUMN, etc) are available here also when in “run script” mode.

You can also run script files. Just enter the full path of the file and put an @ in front. Then highlight it and run it. Like this:
@c:\temp\my_script.sql

Building Scripts

Usually you will build script files that you then run via SQL Plus. It’s important to know a few things so that your scripts run smoothly. A well constructed script should be readable, logically laid out, use comments where appropriate and should generate some sensible output along the way.

Comments

You have 3 options for comments (i.e. not actual code):

• Use “rem” (remark) at the beginning of a line to signal a remark follows. For example:
  rem Author rod
  This can only be used for a “single-line” comment. And it can only be used “outside” of any statements. So, it’s good for maybe descriptive headers in a file only. “rem” is a SQL Plus/SQL Developer command, so other tools do not understand it. There are better options!
• Use a double-hyphen for a single line comment. Can be used anywhere, but can’t have code after it. Examples:
  -- Script: mem_tuning.sql
-- Purpose: Display memory related statistics

DELETE FROM my_tab WHERE status = 5; -- delete all processed items
-- Now commit what we have done
COMMIT;
• Use a block comment. This is the most flexible as it can be used anywhere, including within a statement and it’s multi-line. For example:
  /* Delete all records that are:
processed
at least 10 days ago
*/
DELETE FROM my_tab WHERE status = 5 /* Processed */ AND
created_date < sysdate - 10;

Blank Lines

Blank Lines help with readability of your scripts. So using blank lines is a must. They don’t cost anything, so use them! Follow these basic rules for blank lines:

• Avoid blank lines in the middle of a plain SQL statement.
• PL/SQL statements can contain blank lines (and should for readability). But the above rule still applies to SQL statements within PL/SQL – no blank lines in the middle of them.
• Blank lines are fine between separate SQL statements and PL/SQL statements.

Script Output

It’s good practice to generate output from your scripts. To provide logging for what was done. You can use the SQL Plus “PROMPT” command for this. It’s the equivalent of using ECHO/echo. Here is an example:
PROMPT Removing processed records more than 10 days old...

Generating output from within PL/SQL code is more difficult. You have to use the dbms_output.put_line procedure to generate the output. In addition the output will only be generated if you have executed SET SERVEROUTPUT ON [SIZE 100000]. Please note that this type of output is only displayed once the PL/SQL statement completes. So if you are iterating through a loop, don’t expect to see output generated on each iteration. Example:

SET SERVEROUTPUT ON
DECLARE
    l_char VARCHAR2(1);
BEGIN
    FOR i IN 0 .. 127 LOOP
        l_char := CHR (i);
        dbms_output.put_line ('Saving record for character: ' || l_char);
        INSERT INTO ascii_tab(id, character) VALUES(i, l_char); 
    END LOOP; 
    COMMIT; 
END;

To save the output to a log file, you can use the SQL*Plus SPOOL command. For example:
spool c:\temp\my_script.log
prompt Sample output
spool off

Putting It All Together

Here is a sample script that puts many of these concepts together (SQL*Plus (only) commands highlighted)…

/*
    Script Name:    good_script.sql
    Purpose:        Demonstrate good scripting
    Parameters:     None
    History:
        Rod H       18-jul-2023     Created
        Rod H       19-jul-2023     Improved logging
*/

SPOOL c:\temp\good_script.log

PROMPT Starting good_script.sql
PROMPT /* blank line in output */



-- Deleting processed records more than 10 days old.  This is a line comment.
PROMPT Deleting processed records more than 10 days old...
PROMPT 

-- Clear records older than 10 days.  Demonstrate in-line block comment
DELETE FROM my_tab WHERE status = 1 /* Processed */ AND 
created_date < sysdate - 10;

COMMIT;



-- Display unprocessed records
PROMPT Unprocessed records...
PROMPT

-- Output formatting
SET PAGESIZE 1000
COL ID FOR 99999
COL CREATED_BY FOR A30
COL CREATED_DATE FOR A20

SELECT id, created_by, TO_CHAR(created_date, 'DD-MON-YYYY hh24:MI') AS created_date
FROM my_tab WHERE status = 0
ORDER BY created_date DESC;



PROMPT Updating department name...
PROMPT
SET DEFINE OFF

UPDATE dept SET department_name = 'Murders & Executions' 
WHERE dept_no = 50;

SET DEFINE ON



PROMPT Populating ascii_tab...
PROMPT

-- PL/SQL block.  Notice the trailing /
DECLARE
    l_char VARCHAR2(1);
BEGIN
    FOR i IN 0 .. 127 LOOP
        l_char := CHR (i); -- Convert the ASCII code to a character
        --
        BEGIN
            INSERT INTO ascii_tab(id, character) VALUES (i, l_char); 
            dbms_output.put_line ('Saved record for character: ' || l_char);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                dbms_output.put_line ('record for character ' || l_char || ' already exists'); -- Ignore duplicate values
        END;
    END LOOP; 
    
    -- Commit everything after the loop
    COMMIT; 
END;
/

SPOOL OFF

For more blogs and interesting information, visit the Cheeky Cockatoo homepage.