Re: How to pass value from SQL*Plus to Unix scripts?

From: <Reginald.W.Bailey_at_jpmorgan.com>
Date: Tue, 22 Oct 2002 15:33:30 -0800
Message-ID: <F001.004F07AE.20021022153330@fatcity.com>

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" <ashoke.k.mandal_at_medtronic.com>@fatcity.com on 10/22/2002 04:54:18 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: ashoke.k.mandal_at_medtronic.com

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: ListGuru_at_fatcity.com (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: Reginald.W.Bailey_at_jpmorgan.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Oct 22 2002 - 18:33:30 CDT