RE: sqlplus from Unix...checking for errors etc.

Answers :

1. Use 'tnsping' command to see if the Oracle instance is available
2. You can use 'whenever oserror ...' or 'whenever sqlerror ...' to return static return codes.
3. See www.orafaq.com under UNIX and they have three methods listed there. I'm reproducing them here ...

How can I SELECT a value from a table into a Unix variable?

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

-----Original Message-----
From: John Dunn [mailto:john.dunn_at_sefas.co.uk] Sent: Monday, October 09, 2000 8:41 AM
To: Multiple recipients of list ORACLE-L Subject: sqlplus from Unix...checking for errors etc.

I want to run sqlplus from a Korn Shell script.

I see 3 problems. :

1. Can I check Oracle is available before running sqlplus?
2. How should I check for sqlplus errors?
3. How can I return variables from sqlplus to the shell script?

The sql is simple(see below). I want to return var_status and var_error_message to the Korn Shell script

var_status VARCHAR2(25);
var_error_message VARCHAR2(100);

BEGIN var_status := get_customer_status('CUSTOMER', 0, 'PHASE1', var_error_message);
END;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

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