Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

11.6 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 better, the
SQL statement to count tables is embedded into a PL/SQL script. A
call to DBMS_OUTPUT.PUT_LINE writes the count to standard output,
which in turn redirects to the tabs file. The
SET SERVEROUTPUT ON command is critical here because it causes
SQL*Plus to actually process output generated using the DBMS_OUTPUT
package. SET FEEDBACK OFF prevents the message
"PL/SQL procedure successfully
completed." from being included in
tabs. After SQL*Plus exits, the contents of
tabs is placed into the shell variable
tabcount.

Example 11-37. Spooling to a file

sqlplus -s gennick/secret << EOF
SET PAGESIZE 0
SPOOL tabs
SELECT COUNT(*) FROM user_all_tables;
EXIT
EOF
tabcount=`cat tabs`
echo You have $tabcount tables.

Example 11-37 functions on lines similar to Example 11-36. This time, rather than being redirected
through standard output, the table count is spooled to the
tabs file. SET PAGESIZE 0 ensures that
tabs remains free of column headings, page
headings, and the like. The remainder of the script is identical to
Example 11-36.

Example 11-38 shows an approach that avoids the need
to write anything to a file.

Example 11-38. Capturing standard output directly to a shell variable

#!/bin/bash
tabcount=`sqlplus -s gennick/secret << EOF
SET PAGESIZE 0
SELECT COUNT(*) FROM user_all_tables;
EXIT
EOF`
echo You have $tabcount tables.

Example 11-38 treats the entire SQL*Plus session as if
it were a file. The backticks (`) enclosing the
command cause all of SQL*Plus's standard output to
be captured and placed into the tabcount shell
variable. SET PAGESIZE 0 ensures that the only output to be captured
is the table count.

The methods in Examples Example 11-36 through Example 11-38 are all good for getting database data into
shell script variables. Don't get too fancy with any
of these methods. They are all suited for scalar data. Unix and Linux
shell script variables
aren't designed to handle esoteric datatypes such as
Oracle's object types, arrays, etc.