topic Re: Shell Scripts in Operating System - HP-UX

Shell Scripts

Rajkumar_3 — Thu, 01 Nov 2001 05:38:35 GMT

Hai All ,

I have a shell script which executing successfully..In this shell script.....

process.sh (Its a file name)
--------------
###
#!bin/sh
/oracle/bin/sqlplus << EOF
spool /oracle/error.log
connect system/password
@/oracle/db_crt.sql
@/oracle/db_run.sql
disconnect
exit

EOF
###################

If i execute that script it will connect to the user system and it will execute the SQL files. Its running fine,But if any error occurs while executing the first script it will continue to the next SQL file...Is it possible to exit if any errors occurs while executing the first SQL script?????

I need the condition like this:
---------------------------
If < SUCCESS> THEN
Continue NEXT
Else
exit

Can any one help me?????

Regards
Ra

Re: Shell Scripts

Deepak Extross — Thu, 01 Nov 2001 06:40:23 GMT

Hi,

Heres a kluge u can try..till someone comes up with a more elegant solution :)

Split the sqlplus invocation into two separate sqlplus invocations - the first one does a "@/oracle/db_crt.sql", while the second does a "@/oracle/db_run.sql".

Redirect the output of the first sqlplus to a scratch file.
Before calling the second sqlplus, grep for an error message / error code in the scratch file. If you find any, exit.

Re: Shell Scripts

Wodisch — Thu, 01 Nov 2001 06:44:13 GMT

Hello Rajkumar,

this is more a "database" question, than a scripting one, but anyway: try the command "set stoponerror on" - if may not be supported in "sqlplus" but it IS working inside "svrmgrl".
Then after the first error your session stops.

HTH,
Wodisch

Re: Shell Scripts

Rajkumar_3 — Fri, 02 Nov 2001 06:34:54 GMT

Hai Wodish,

If i set that clause in my sql script at SVRMGRL prompt, If the error occurs in the first file it will exit.. but it will continue with the second sql script because i am using again the same syntax for another sql file as mentioned above..Is there any other alternative way to accomplish this task??

Regards
Raj

Re: Shell Scripts

Thierry Poels_1 — Fri, 02 Nov 2001 08:23:38 GMT

Hi,

please note it is bad practice to put (Oracle) passwords in scripts for several obvious reasons: possible security breach, scripts need to be changed if password changes, ....

I would opt for externally authenticated password (OPS$ login) for this user, with limited privleges, enable crontab, and disable login on Unix if possible.

regards,
Thierry

Re: Shell Scripts

ramesh_6 — Fri, 02 Nov 2001 10:13:45 GMT

Hi,

Follow the steps to exit if not successful

yousqlscript.sql
if ( `echo $?` != 0)
then
echo "Script Execution Failure"
exit 0

Hope this will be the right answer for your question

Re: Shell Scripts

Thierry Poels_1 — Fri, 02 Nov 2001 11:05:36 GMT

Hi,

If you want to test the exit code of your SQL-prog, then make sure your SQL-script generates an exit code!!

sqlplus / << TTT
select * from doesnotexist;
exit
TTT
====> exit code = 0 !!!

sqlplus / << TTT
whenever sqlerror exit sql.sqlcode;
select * from doesnotexist;
exit
TTT
====> exit code = 174

BUT:
sqlplus / << TTT
whenever sqlerror exit sql.sqlcode;
select * from dual where 1=2;
exit
TTT
====> exit code = 0 !! (no records found = no error!)

sqlplus / << TTT
whenever sqlerror exit sql.sqlcode;
declare
n number;
begin
select * into n from dual where 1 = 0;
end;
/
exit
TTT
====> exit code = 123 (PL/SQL block generates error when exception is not handled)

good luck,
Thierry.

Re: Shell Scripts

Graham Cameron_1 — Fri, 02 Nov 2001 15:04:53 GMT

Unless I am missing something, all I think you need is to add a line
WHENEVER SQLERROR EXIT
after the connect, before the first script.
Unless either of the called scripts undoes this, eg with
WHENEVER SQLERROR CONTINUE
that should give the result you seek.

Re: Shell Scripts

harry d brown jr — Fri, 02 Nov 2001 15:31:32 GMT

Try perl and the perl::dbi

http://www.saturn5.com/~jwb/dbi-examples.html

Other examples:

http://www.cbc.umn.edu/~mayer/oratut/USfacts.html

http://www.cbc.umn.edu/~mayer/oratut/USgov.html

live free or die
harry