Re: How to return an value from Oracle to Unix in a shell scrtip.

Hi

    the following where taken from oracle underground FAQ's (http://www.orafaq.org/faq2.htm)

You can select a value from a database column directly into a Unix shell variable. Look at the following shell script examples:
#!/bin/sh

VALUE=`sqlplus -silent "user/password_at_instance" <<END set pagesize 0 feedback off verify off heading off echo off select max(c1) from t1;
exit;
END`
if [ -z "$VALUE" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $VALUE
fi

Second example, using the SQL*Plus EXIT status code:
#!/bin/ksh

sqlplus -s >junk1 "usr/psw_at_instance" <<EOF column num_rows new_value num_rows format 9999 select count(*) num_rows
  from table_name;
exit num_rows
EOF
echo "Number of rows are: $?"

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.
sqlplus -s usr/psw_at_instance |& # Open a pipe to SQL*Plus

print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- "set sqlprompt ''"

print -p -- "select sysdate from dual;"
read -p SYSDATE

print -p -- "select user from dual;"
read -p USER

print -p -- "select global_name from global_name;" read -p GLOBAL_NAME

print -p -- exit

echo SYSDATE:     $SYSDATE
echo USER:        $USER
echo GLOBAL_NAME: $GLOBAL_NAME

HTH Conan Received on Mon Oct 23 2000 - 06:48:18 CDT