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.

     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net