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.