Shell Scripts

[Rajkumar_3]

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

[Deepak Extross]

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.

[Wodisch]

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

[Rajkumar_3]

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

[Thierry Poels_1]

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

[ramesh_6]

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

[Thierry Poels_1 · Accepted solution]

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.

[Graham Cameron_1]

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.

[harry d brown jr]

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