Korn ShellThis is a featured page

This page is currently a placeholder for Korn Shell related info - i.e. how to write Korn Shell scripts that interact w/ Oracle.

Here's a piece of code that extracts an Oracle database value into a shell script variable:

typeset -ix PAC_CNT=`sqlplus -s / <<-EOF
SET heading off
SET feedback off
SET NUMFORMAT 99999999
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT SQL.OSCODE
SELECT count(0)
FROM all_objects ao
WHERE ao.owner = 'MY_OWNER'
AND ao.object_name = 'MY_PACKAGE'
AND ao.object_type = 'PACKAGE';
EOF`



If you use both shell scripts and Oracle, you will frequently want to refer in a shell script to a value that is found in an Oracle database. This pattern shows a way to retrieve an Oracle value into a Korn shell script variable.

#!/bin/ksh
######
#
# Function get_idx returns a value from Oracle to UNIX.
# By using the PRINT command within SQL*Plus and calling this
# function from within an 'export' call in UNIX, the value printed
# in SQL*Plus is captured in a UNIX environment variable.
#
# WATCH THE SPACING AND THE PLACEMENT OF THE SQL END OF FIELD
# MARKER. FOR SOME REASON, IN THE FUNCTION DEFINITION, THIS
# MAKES A DIFFERENCE.
#
#####

function get_idx
{
sqlplus -s / > ${SQL_LOG_FILE} <<-SQLEOF
SET FEEDBACK OFF
SET HEAD OFF
SET VERIFY OFF
WHENEVER SQLERROR EXIT sql.SQLCODE
WHENEVER OSERROR EXIT OSCODE
SET SERVEROUTPUT ON SIZE unlimited
VARIABLE RETVAL NUMBER
BEGIN
SELECT 150 -- Hardcoded 150 here, but could be any value or sequence
INTO :RETVAL
FROM DUAL ;
END ;
/
print :RETVAL
exit
SQLEOF
}

# Call the function during an export call to a variable, which sets
# the UNIX variable to the value from SQL*PLus
export DJVAL=$(get_idx)

# Look at what we got
echo '......The env var set in SQL*Plus is: '${DJVAL}




No user avatar
pvzweden 		Latest page update: made by pvzweden , Jun 27 2008, 2:35 AM EDT (about this update About This Update pvzweden Edited by pvzweden


view changes
- complete history)
Keyword tags: korn shell script
More Info: links to this page

Threads for this page

There are no threads for this page.  Be the first to start a new thread.