RE: Returning Data from SQL to its calling UNIX Shell

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Wed, 20 Sep 2000 14:26:45 -0400
Message-Id: <10625.117480@fatcity.com>

Direct the output of the sql command to a logfile. Then either cat or awk the output into shell variables. Works well here for several scripts.

Hope this helps.
Steve

Steven Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
MonaghaS_at_mscdirect.com

cut ----- cut ----- cut ---- sqlplus -s scott/tiger @next_extent_fail.sql > $LOG_FILE

#
# If any rows are returned, parse the data based on priority
#

WC_RESULT=`wc -l $LOG_FILE`
LINES=`echo ${WC_RESULT} | awk ' { print $1 } ' ` if [ ${LINES} -gt 0 ]
then

cat ${LOG_FILE} | while read lineitem do

      PRIORITY=`echo ${lineitem} | awk '{ print $1 }'`
      if [ $PRIORITY -eq 1 ]
      then
         TEMP1=`echo $lineitem | awk '{print "Database object "$2"."$3"
cannot extend by "$4" in tablespace "$5}'`
         echo $TEMP1 >> $HIGH_MSSG
      elif [ $PRIORITY -eq 2 ]
      then
         TEMP1=`echo $lineitem | awk '{print "Database object "$2"."$3"
cannot extend twice by "$4" and "$5" in tablespace "$6}'`
         echo $TEMP1 >>$MEDIUM_MSSG
      fi

done
fi
----- cut ----- cut ----- cut ----

> -----Original Message-----
> From: Suri, Deepak [mailto:DSuri_at_oxhp.com]
> Sent: Wednesday, September 20, 2000 3:01 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Returning Data from SQL to its calling UNIX Shell
>
>
> Is there a way that allows me to return an alphanumeric string from an
> SQL*Plus or PL/SQL block to its calling UNIX shell ?
>
> The one way I can think of is embeding the PL/SQL code within a Pro*C
> wrapper and using host variables to return the value.
>
> Any other suggestions ?
>
> thanx
> deepak
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Suri, Deepak
> INET: DSuri_at_oxhp.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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 Wed Sep 20 2000 - 13:26:45 CDT