Korn Shell |

Version Compare

Back to page history

Version User Scope of changes
Jun 27 2008, 2:35 AM EDT (current) pvzweden
Jun 27 2008, 2:35 AM EDT pvzweden 23 words added, 11 words deleted

Changes

Key:  Additions   Deletions
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}