Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Returning Values to Unix

When writing shell scripts, you can use SQL*Plus as a mechanism for getting information from your database into shell script variables. You can do this in several ways. If you need to return a small numeric value, you can use the EXIT command. Example 11-35 uses the EXIT command to return a count of tables to a shell script variable.

Example 11-35. Returning a value through the EXIT command

#!/bin/bash
sqlplus -s gennick/secret << EOF
COLUMN tab_count NEW_VALUE table_count
SELECT COUNT(*) tab_count FROM user_all_tables;
EXIT table_count
EOF

let "tabcount = $?"
echo You have $tabcount tables.

Passing data back through the EXIT command is of limited usefulness. The technique is good only for numeric values between 0 and 255 (on Unix/Linux systems), and it precludes access to success or failure status.

Another approach to placing a value into a shell script variable is to write a value to a file and use the Unix cat command to place the contents of that file into a variable. Examples Example 11-36 and Example 11-37 show two different variations on this theme.

Example 11-36. Redirecting standard output to a file

#!/bin/bash
sqlplus -s gennick/secret > tabs << EOF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
   tab_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO tab_count
   FROM user_all_tables;

   DBMS_OUTPUT.PUT_LINE(tab_count);
END;
/
EXIT
EOF

tabcount=`cat tabs`
echo You have $tabcount tables.

Example 11-37 redirects standard output to a file named tabs. To control the output ...