2.10. SQL*Plus with Korn Shell

There are three topics covered in this section.

• Invoking SQL*Plus from Korn Shell

• Invoking Korn Shell from SQL*Plus

• Passing arguments between languages

To embed SQL*Plus within Korn Shell, use Korn Shell Here-Documents. This Korn shell language feature allows “input redirection” to specify “in-stream text.” The syntax uses the symbol “<<,” which is followed by any character string—our example uses “EOF”. Following “<< EOF” is the input-stream text consisting of SQL*Plus command file.

The following shows just a Korn Shell script that prints the first two arguments.

#!/bin/ksh
# KSH script to echo to parameters.
echo $1
echo $2
# end of script

Korn shell precedes positional parameters with a “$” while SQL*Plus uses “&”. We can embed a SQL*Plus script within a Korn Shell script and run the Korn Shell script, passing arguments on the command line that are then passed to the SQL*Plus script.

The following Korn shell script accepts three arguments: username, password, and a string used to match column names. The column_name has scope within the SQL*Plus script.

#!/bin/ksh
# KSH script filename: script_01.ksh
username=${1}
password=${2}
column_match=${3}
sqlplus –s ${username}/${password} << EOF
SELECT table_name, column_name
FROM user_tab_columns
WHERE column_name LIKE UPPER ('${column_match}'),
exit
EOF
# end of script

• We need to exit from SQL*Plus—to take us out of SQL*Plus and back to the Korn Shell. Thus we include EXIT, which is the last SQL*Plus statement within this script.

• The “-s” is a “Silent Mode” option that suppresses superfluous messaging by SQL*Plus.

• Within the SQL*Plus script, the Korn Shell parameter notation ($) is used.

SQL*Plus scripts can be encapsulated into distinct Korn Shell functions. The following table is a description of the Korn Shell functions used in the script.

###############################################
#
# FUNCTION: the_tablenames_are
#
#----------------------------------------------
function the_tablenames_are
{
sqlplus -s $username/$password << EOF
set feedback off
set pagesize 0
set echo off
select table_name from USER_TABLES;
exit;
EOF
}
###############################################
#
# FUNCTION: gen_table_report
#
#----------------------------------------------
function gen_table_report
{
sqlplus -s $username/$password << EOF
set feedback off
set pagesize 0
set echo off
SELECT table_name, column_name FROM
USER_TAB_COLUMNS WHERE table_name = '$1';
exit;
EOF
}
###############################################
#
# MAIN program code
#
#----------------------------------------------
username=${1}
password=${2}

the_table_names_are | while read tn
do
   gen_table_report $tn
done
#
###############################################

SQL*Plus can “host out” to an operating system command using the SQL*Plus host command. Within a SQL*Plus script, you can include the SQL*Plus HOST command followed by any host command. The following is a revision of the aforementioned KSH script—this is just the SQL*Plus portion. This generates a spool file and immediately opens the spool file in an editor window.

SPOOL output
SELECT table_name, column_name
FROM user_tab_columns
WHERE column_name LIKE UPPER ('${column_match}'),
SPOOL off
HOST vi output.lst

When you run the aforementioned script, the output immediately pops up for viewing. The same can be done in Windows using: HOST NOTEPAD OUTPUT.LST.