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

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Tue, 10 Oct 2000 10:43:43 -0700 (PDT)
Message-Id: <10645.118887@fatcity.com>

--0-1957747793-971199823=:25328

Content-Type: text/plain; charset=us-ascii

Tnsping will only tell you if the listener's running. If the listener's up and the instance is down you'll get a false indication that the instance is available.

"Suri, Deepak" <DSuri_at_oxhp.com> wrote: Answers :

Use 'tnsping' command to see if the Oracle instance is available
You can use 'whenever oserror ...' or 'whenever sqlerror ...' to return static return codes.
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" <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" <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-----

Sent: Monday, October 09, 2000 8:41 AM
To: Multiple recipients of list ORACLE-L

I want to run sqlplus from a Korn Shell script.

I see 3 problems. :

Can I check Oracle is available before running sqlplus?
How should I check for sqlplus errors?
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 also send the HELP command for other information (like subscribing).
--

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

Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
--0-1957747793-971199823=:25328

Content-Type: text/html; charset=us-ascii

<P>&nbsp;Tnsping will only tell you if the listener's running. If the listener's up and the instance is down you'll get a false indication that the instance is available.<BR>
<P>&nbsp; <B><I>"Suri, Deepak" &lt;DSuri_at_oxhp.com&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Answers :<BR><BR>1. Use 'tnsping' command to see if the Oracle instance is available<BR>2. You can use 'whenever oserror ...' or 'whenever sqlerror ...' to return<BR>static return codes.<BR>3. See www.orafaq.com under UNIX and they have three methods listed there.<BR>I'm reproducing them here ...<BR><BR>How can I SELECT a value from a table into a Unix variable?<BR><BR>You can select a value from a database column directly into a Unix shell<BR>variable. Look at the following shell script examples: <BR><BR>#!/bin/sh<BR>VALUE=`sqlplus -silent "user/password_at_instance" &lt;<END<BR>set pagesize 0 feedback off verify off heading off echo off<BR>select max(c1) from t1;<BR>exit;<BR>END`<BR>if [ -z "$VALUE" ]; then<BR>echo "No rows returned from database"<BR>exit 0<BR>else<BR>echo $VALUE<BR>fi<BR><BR>Second example, using the SQL*Plus EXIT status code: <BR>#!/bin/ksh<BR>sqlplus -s &gt;junk1 "usr/psw!

@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----- Sent: Monday, October 09, 2000 8:41 AM To: Multiple recipients of list ORACLE-L I want to run sqlplus from a Korn Shel!

l script.<BR><BR>I see 3 problems. :<BR><BR>1. Can I check Oracle is available before running sqlplus?<BR><BR>2. How should I check for sqlplus errors?<BR><BR>3. How can I return variables from sqlplus to the shell script?<BR><BR>The sql is simple(see below). I want to return var_status and<BR>var_error_message to the Korn Shell script<BR><BR><BR>var_status VARCHAR2(25);<BR>var_error_message VARCHAR2(100);<BR><BR><BR>BEGIN <BR><BR>var_status := get_customer_status('CUSTOMER', 0, 'PHASE1',<BR>var_error_message);<BR>END;<BR><BR><BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>-- <BR>Author: John Dunn<BR>INET: john.dunn@sefas.co.uk<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelli!
ng of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).<BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>-- <BR>Author: Suri, Deepak<BR>INET: DSuri@oxhp.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).<BR></BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>

Received on Tue Oct 10 2000 - 12:43:43 CDT