Here is an example that I created years ago using the Korn shell.
load_status=0
SQLSCRIPT=createLoadRecord.sql
LOGFILE="${TEMPDIR}/createLoadRecord${ID}.log"
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
print "The Source CRS is ${SOURCECRS} \n"
SQLSTMT="set serveroutput on;
set heading off;
set feedback off;
set termout off;
set echo off;
set verify off;
set linesize 120;
set pagesize 300;
set recsep wrapped;
spool ${LOGFILE};
VAR loadid NUMBER;
execute :loadid := oe_hdl_create_a_load('${LOADTYPE}', '${USER}',
'${PROJECT}', '${PRJSID}', '${SOURCEMEASSYS}', '${SOURCECRS}' , \
'${TEMPDIR}', '${VALREFVALUES}', '${CREATEPARENT}', '${DUPDATA}',
${MAXEXCEPTIONS} ,'${DATEFORMAT}', '${SORTEDINDEXES}' ,'${DATAMODEL}');
print loadid;"
print "Attempting to capture the LOADID"
LOADID=`echo "${SQLSTMT}" | ${SQLPLUS} -s ${SQLCONNECT}`
LOADID=`echo ${LOADID} | tr -d " "`
loc_loadid=${LOADID}
The two important lines are the echo lines which execute the SQL statement and then
trim off the excess whitespace. The shell variables are replaced
with actual values
during the interpetation. You can also do it as a shell Here document. In SQL Plus
ensure that it exits with the value you are trying to return. For
instance, set a variable using DEFINE , then exit at the end of the SQL script using
EXIT &varname. The shell
script would have a variable set to capture the output, like in the example above.
I hope this helps.
RWB
"Mandal, Ashoke" <[EMAIL PROTECTED]>@fatcity.com on 10/22/2002
04:54:18 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Greetings,
SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
US7ASCII
Now in the Unix shell script I want to pass this character set as follows
/user/oracle/export/export_db.sh /uexports tcqscm system manager
AMERICAN_AMERICA.US7ASCII
How can I pass the character set to the export script?
Thanks,
Ashoke
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
