Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to return an value from Oracle to Unix in a shell scrtip.

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

From: Conan <conan_at_(no-spam)as-if.com>
Date: Mon, 23 Oct 2000 12:48:18 +0100
Message-ID: <fAVI5.9941$Bw1.6019@news.indigo.ie>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US