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


sqlplus -s gennick/secret << EOF

COLUMN tab_count NEW_VALUE table_count

SELECT COUNT(*) tab_count FROM user_all_tables;

EXIT table_count


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


sqlplus -s gennick/secret > tabs << EOF




 tab_count NUMBER;


 SELECT COUNT(*) INTO tab_count

 FROM user_all_tables;






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


SPOOL tabs

SELECT COUNT(*) FROM user_all_tables;



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


tabcount=`sqlplus -s gennick/secret << EOF


SELECT COUNT(*) FROM user_all_tables;



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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

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

Similar book on Amazon © 2008-2020.
If you may any questions please contact us: