How do I Interact With Oracle's SQL*Plus

This piece is by a guy called Ian Craig, reproduced here with his permission.

I've removed his email address at his request. I will be maintaining this version of his article, so queries, corrections and suggestions to me please -- Credit for a good article to Ian.

Mike Lacey


________________________________________________________________________________
Interacting with Oracle via the UNIX Korn shell.
________________________________________________________________________________

Toolbox:
A reasonable understanding of SQL, PL/SQL and UNIX Korn shell scripting is required to follow this article. The examples given have been tested against the following configuration: Oracle Enterprise Edition 8.1.5 for Linux, Linux version 2.2.7 and the public domain Korn shell "pdksh" version 5.2.13. If you're a bit shaky on your Korn shell scripting, my favorite book on the subject is "The new Korn Shell" by Morris Bolsky and David Korn [Prentice-Hall PTR, ISBN: 0-13-182700-6].

For DBAs and developers alike, UNIX remains a fabulous choice for hosting Oracle databases. Both technologies have proven to be more flexible and more stable for more time than most of us care to remember, but I still find the two 'old dogs' are ready for any new trick I care to throw them.

The remarkable 'Korn' shell, when coupled with the strength of the standard UNIX tool set, seems to be able to extract the best from any programmer. While many Oracle customers shy away from actively adopting and supporting UNIX development, most are perfectly happy to accept 'shell script', and 'shell script' is all we need for many back-end applications. This article demonstrates how SQL*Plus can be used in the same manner as any UNIX tool. And you will find that interacting with an Oracle database via a 'shell script' program is simple, flexible and dynamic--the UNIX way.


Background / Overview
SQL*Plus will happily integrate into the standard UNIX IO streams. The simplest way to demonstrate this point is that the following UNIX command:

print "select * from dual;" | sqlplus -s scott/tiger

produces the following output:


D
-
X

[Note: the '-s' flag suppresses the standard Oracle banner.]

Once you grasp this, the possibilities for using SQL*Plus from UNIX can become quite exciting. You can construct and run Dynamic SQL or PL/SQL statements without difficulty. There is also a mechanism available to a shell script programmer that avoids commit or rollback processing between SQL*Plus transactions.

By using pipes to deliver SQL or PL/SQL statements to SQL*Plus, you can avoid the use of temporary files and construct SQL or PL/SQL statements 'on-the-fly'. You can also use pipes to collect the output generated from SQL*Plus (thereby avoiding temporary files, once again), and interpret the output of pipes using the UNIX tool set.

This article uses a simple UNIX file to Oracle table interface to demonstrate UNIX/Oracle communications techniques.


Is Oracle working properly?
One of the first tasks required of an interface is to check that the target Oracle database is actually ready for business. I've seen and used many methods to do this, but my current favorite is to run a small predictable query and check the result. If the query performs as expected, then it is likely that Oracle is OK. The following function Is_Oracle_OK does the job:

#!/usr/bin/ksh
typeset -irx TRUE=0
typeset -irx FALSE=1

function Is_Oracle_OK
{
if print "
select dummy||'OK' from dual;
" | sqlplus -s scott/tiger | grep -q XOK
then return ${TRUE}
fi
return ${FALSE}
}

if Is_Oracle_OK
then print "Oracle is up"
else print "Oracle is down"
fi

This script queries the "dual" table and then scans the output for the string that is expected if everything is working well ("XOK"). Note that care has been taken to ensure that if Oracle were to reject the submitted SQL, any fragment of an Oracle-generated error report will not meet the acceptance criteria set within the grep command.


Dynamically Generated SQL
Now that we have established that the database is operational using the Is_Oracle_OK function, we want to insert some of the contents of the /etc/passwd file into a table. In this example, we want to load the first and fifth fields (delimited by '|') of each line that begins with the character 'r'. To add a little extra spice, the following example code creates, populates, queries and drops the example table. We issue a commit every time 10 records are inserted and after the last insert. Note the use of UNIX environment variables within the SQL statements.

{
typeset TABLE='example' # Name the table to load

print "WHENEVER SQLERROR EXIT"
print "WHENEVER OSERROR EXIT"
print "set autocommit off"

print "create table ${TABLE}
(
user_name varchar2(16),
user_description varchar2(64)
);"

typeset -i COUNT=0 # Count records inserted
typeset FILE=/etc/passwd # The file to load
typeset Item1 # Holds user name from passwd file
typeset Item5 # Holds user description

grep -E '^r' ${FILE} | while read LINE
do
let COUNT=COUNT+1
Item1=$(print ${LINE} | cut -d'|' -f1)
Item5=$(print ${LINE} | cut -d'|' -f5)

print "
insert into ${TABLE}
(
user_name,
user_description
)
values
(
'${Item1}',
'${Item5}'
);"

# Commit every 10 records - COUNT ends with a '0'
[[ ${COUNT} == +([0-9])0 ]] && print "commit;"
done
print "commit;" # Issue a commit after the last insert

print "select * from ${TABLE};"
print "drop table ${TABLE};"
} | sqlplus -s scott/tiger

In my shell scripts, I usually prefer to turn autocommit off and take charge of issuing the commits myself, because I can control the frequency. Note that the shell variables Item1 and Item5 are single-quoted within the insert SQL because they are strings. Don't quote numbers!

Watch out for single-quote characters contained within the data source, they'll cause the SQL statements to fail unless you escape them or remove them.


Keeping Track of Progress
Although I would be fairly happy to use the code given above for a one-off interface, it is a bit basic. The main problem is that it doesn't check to see what Oracle had to say about all those SQL statements we threw at it. This is not difficult to overcome, however, as the SQL*Plus output can be piped to a module that performs the necessary checks.
A neat trick to use in some situations (like debugging) is to save all the SQL*Plus input and output to a file. Try replacing the "} | sqlplus -s scott/tiger" command (on the last line of the previous example code), with: "} 2>&1 | tee -a /tmp/ora.txt | sqlplus -s scott/tiger 2>&1 | tee -a /tmp/ora.txt".

While this should work nicely, I prefer to replace all the 'sqlplus -s scott/tiger' strings within the body of my code with an alias, because this allows me to offer a 'debug oracle' facility, which I can activate to record the Oracle IO. To do this, the beginning of my Oracle /shell-script code includess the following :


#!/usr/bin/ksh
typeset -irx TRUE=0 # My boolean 'true' - return code
typeset -irx FALSE=1 # My boolean 'false' - return code
typeset -r DEBUG=${TRUE} # Debug is ON - you can change this to FALSE

if (( ${DEBUG} == ${TRUE} ))
then # Debug mode, all Oracle IO saved to file
alias Tracle="tee -a /tmp/ora.txt |
sqlplus -s scott/tiger 2>&1 |
tee -a /tmp/ora.txt "
else # Normal mode, Oracle IO is not recorded
alias Tracle="sqlplus -s scott/tiger"
fi

Once the alias has been defined, I can use the alias Tracle instead of the string 'sqlplus -s scott/tiger' within the body of my code; for example:


print "select * from dual;" | Tracle


Quick Error Check
If SQL*Plus encounters an error, it generally reports the problem with an error code prefixed with either ORA, ERROR, or (lately) SP2. Armed with this knowledge, I can test that some Oracle interactions have worked correctly by simply scanning the output for an occurrence of an error prefix, for example:

if print "
create table ${TABLE}
(
user_name varchar2(16),
user_description varchar2(64)
);" |
sqlplus -s scott/tiger |
! grep -qiE 'ora|error|sp2'
then print "Table ${TABLE} created"
else print "An error was detected when creating table ${TABLE}"
fi

Notice that grep will return true in the above example if one or more of the error prefixes (either case) are found in the output. I've negated this (using the exclamation mark) to keep with the UNIX 'innocent until proven guilty' philosophy. Ensure that the expected output does not contain the error code prefixes, or you'll find stormy weather ahead.

Once again, I like to define an alias for this call through SQL*Plus, but a function that takes the SQL as an argument could also be used to make things even prettier:


alias Tracle_OK="sqlplus -s scott/tiger 2>&1 | ! grep -qiE 'ora|error|sp2'"

The code now looks like this:


if print "
create table ${TABLE}
(
user_name varchar2(16),
user_description varchar2(64)
);" | Tracle_OK
then print "Table ${TABLE} created"
else print "An error was detected when creating table ${TABLE}"
fi


The BIG 'commit' problem
When a SQL*Plus session terminates, all uncommitted transactions are either lost or committed (depending on your setup). Using the method I've just outlined, this makes things a bit difficult if you want to check how things are going without ending the current SQL*Plus session. To counter this problem, I could set a SQL*Plus session as a co-process and communicate with it using the print -p and read -p shell commands. For example, initiate a SQL*Plus session as a co-process with the following command:

sqlplus -s scott/tiger |&

Now send two commands to the co-process:


print -p "select * from dual;"
print -p "exit"

Not much seems to happen, but the following code:


while read -p LINE
do
print - ${LINE}
done

retrieves the SQL*Plus output:


D
-
X

Note: UNIX also reports that the co-process has completed.

Unfortunately, the read -p command will hang if there is no output to collect or the output stream has not been terminated. This can even happen if you're just too quick in trying to grab the output while Oracle is still cogitating. This method, however, does offer the programming prize of being able to fully communicate with Oracle via a single, unbroken SQL*Plus session.

To prevent the read command from hanging my programs, I place a marker in the output stream (for example, using the SQL*Plus 'prompt' command) after requesting some work. I then ensure that the code does not read beyond that marker. Placing the marker also gives the system the moment required to prevent the 'too quick' hang mentioned earlier.


sqlplus -s scott/tiger |& # Start the sqlplus co-process

print -p "select * from dual;" # Issue SQL to co-process (sqlplus)
print -p "prompt marker1" # Place marker on output

while read -p LINE # Read all lines from co-process
do
[[ "${LINE}" == "marker1" ]] && break # Break loop if marker has been read
print - ${LINE} # Collect the output generated by SQL
done

Newer versions of the Korn shell offer a time-out argument for the read command.


PL/SQL
When using the methods outlined in this article, PL/SQL can be used in just the same way as SQL. For example:

sqlplus -s scott/tiger |& # Start sqlplus as co-process

print -p "begin
null;
end;" # Define an anonymous PL/SQL block
print -p '/' # Execute the PL/SQL
print -p "exit" # Terminate the co-process

while read -p LINE # Get the co-process output
do
print - ${LINE}
done

produces the output:


PL/SQL procedure successfully completed.


Conclusion
The advantage of Oracle shell scripts is that you can apply the full range of UNIX tools to the Oracle data. Given that the Oracle interface is reasonably simple, the productivity boost gained by a shell scripter using UNIX to manipulate the data can be significant.
And always remember that there is more than one way to do it.

Ian Craig