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

--0-1649760492-971099650=:21413
Content-Type: text/plain; charset=us-ascii

 You can check to see if the instance is running in serveral ways. One way if you're running sqlplus on the same machine as the database is to look for the background processes with the PS command.

ps -ef | grep ora_smon_$ORACLE_SID && echo "instance is up" || echo "instance is down"

The only time this will give you a problem is if the instance is started but not opened. To get around that, or if you're running sqlplus on a different machine than the instance, log in with sqlplus, create an o/s file from sqlplus and log out. If the file exists, you logged in okay and the db is up. If not, the db is down or you provided an invalid username or password.

rm dummy 2>/dev/null
sqlplus -s <<! >/dev/null 2>&1
user/password_at_global_name
host touch dummy
!

[[ -f dummy ]] && echo "Instance is up" || echo "instance is down"

To check for sql errors use the sqlplus whenever command.

sqlplus -s <<!
user/password
whenever sqlerror exit sql.sqlcode
...
!

This will cause sqlplus to immediately fail on any sql error and return an error code to the shell. The code doesn't exactly match the sql error code because sqlplus returns 16 bit return codes while the shell can only handle 8 bits. The return code you get will be low order 8 bits of the error code. If you want the entire error code you'll have to grep the output of sqlplus for ERROR and look at the next line.

To return variables to the shell the easiest way is to print them in sqlplus and issue a shell read command.

#!/bin/ksh
sqlplus -s <<! | read varstatus
user/password
set pages 0 feedback off
var var_status VARCHAR2(25);
var var_error_message VARCHAR2(100);
BEGIN
:var_status := get_customer_status('CUSTOMER', 0, 'PHASE1',:var_error_message); END;
!

echo $varstatus $var_error_message

Hope this helps.

  Chuck Hamilton

  John Dunn <john.dunn_at_sefas.co.uk> wrote: 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
also send the HELP command for other information (like subscribing).



---------------------------------
Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free!
--0-1649760492-971099650=:21413
Content-Type: text/html; charset=us-ascii

<P> You can check to see if the instance is running in serveral ways. One way if you're running sqlplus on the same machine as the database is to look for the background processes with the PS command.</P>
<P>ps -ef | grep ora_smon_$ORACLE_SID &amp;&amp; echo "instance is up" || echo "instance is down"</P>
<P>The only time this will give you a problem is if the instance is started but not opened. To get around that, or if you're running sqlplus on a different machine than the instance, log in with sqlplus, create an o/s file from sqlplus and log out. If the file exists, you logged in okay and the db is up. If not, the db is down or you provided an invalid username or password.</P>
<P>rm dummy 2&gt;/dev/null<BR>sqlplus -s &lt;&lt;! &gt;/dev/null 2&gt;&amp;1<BR>user/password_at_global_name<BR>host touch dummy<BR>!<BR>[[ -f dummy ]] &amp;&amp; echo "Instance is up" || echo "instance is down"</P>
<P>To check for sql errors use the sqlplus whenever command.</P>
<P>sqlplus -s &lt;&lt;!<BR>user/password<BR>whenever sqlerror exit sql.sqlcode<BR>...<BR>!</P>
<P>This will cause sqlplus to immediately fail on any sql error and return an error code to the shell. The code doesn't exactly match the sql error code because sqlplus returns 16 bit return codes while the shell can only handle 8 bits. The return code you get will be low order 8 bits of the error code. If you want the entire error code you'll have to grep the output of sqlplus for ERROR and look at the next line.</P>
<P>To return variables to the shell the easiest way is to print them in sqlplus and issue a shell read command.</P>
<P>#!/bin/ksh<BR>sqlplus -s &lt;&lt;! | read varstatus<BR>user/password<BR>set pages 0 feedback off<BR>var var_status VARCHAR2(25);<BR>var var_error_message VARCHAR2(100);<BR>BEGIN <BR>:var_status := get_customer_status('CUSTOMER', 0, 'PHASE1',:var_error_message);<BR>END;<BR>!<BR>echo $varstatus $var_error_message</P>
<P>Hope this helps.<BR><BR><EM>&nbsp; </EM>Chuck Hamilton</P>
<P>&nbsp; <B><I>John Dunn &lt;john.dunn_at_sefas.co.uk&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">I want to run sqlplus from a Korn Shell 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_at_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>
<a href="http://photos.yahoo.com/">Yahoo! Photos</a> - 35mm Quality Prints, Now Get 15 Free!