OraFAQ Forum: Unix » how to trap pl/sql error in shell script

Home » Infrastructure » Unix » how to trap pl/sql error in shell script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to trap pl/sql error in shell script [message #97631]

Wed, 25 September 2002 06:00

Tara
Messages: 4
Registered: July 1999

Junior Member

I have a shell script which calls a packaged procedure. In my shell script, how do I trap an error which has happened in the pl/sql program, so that I can take some actions in the shell script. At the moment, I have an exception handler in my pl/sql program which rolls back the changes.
Thanks in advance,
T.

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #97632 is a reply to message #97631]

Wed, 25 September 2002 21:13

K.K. Raj kumar
Messages: 33
Registered: July 2002

Member

Hello,

You can use
set serveroutput on size 50000 before calling the procedure or package and inside the procedure or package you can use dbms_output.put_line to trap the variables.

Before calling the package you can spool the output so that the messages will come out.

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #97634 is a reply to message #97631]

Sun, 29 September 2002 07:54

Deborah
Messages: 18
Registered: June 2002

Junior Member

It did not work out for me.

But I use the following to catch the details
of the error by redirecting into some file.

sqlplus -s usename/password@connect << EOF >> Logfile.txt
execute pl-sql_procedure_name
exit
EOF

The above is in shell script and I get the
message (success/ failure) in the Logfile.txt

Debby

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #97638 is a reply to message #97631]

Mon, 30 September 2002 16:38

andrew again
Messages: 2577
Registered: March 2000

Senior Member

See the solution at the following URL:

http://www.oracle.com/oramag/oracle/01-may/index.html?o31asktom.html

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #97640 is a reply to message #97638]

Wed, 02 October 2002 14:18

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Note that some things don't generate errors - like connecting to Oracle. If user/pass is wrong (or maybe if Oracle is unavailable) then you don't get a pl/sql error. A good way of handling that is to spool to a dummy file as the last command in the script. When you return to the OS and your dummy file is there (you obviouly clean it up after your run or before the next run) then you know that you got to the bottom of the file.

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #97794 is a reply to message #97638]

Tue, 28 January 2003 02:23

C.Muthukumar
Messages: 1
Registered: January 2003

Junior Member

How to trap oracle stored procedure in the unix shell script.

Report message to a moderator

Re: how to trap pl/sql error in shell script [message #98451 is a reply to message #97631]

Mon, 25 October 2004 08:16

casz
Messages: 1
Registered: October 2004

Junior Member

Try:

sqlplus <<FF
user/pass
whenever sqlerror exit 1;
whenever oserror exit 1;
@file.sql
exit 0;
FF
if [[ $? -ne "0" ]]; then
echo "ERROR"
else
echo "OK"
fi

Report message to a moderator

Previous Topic:	TIMEZONE ON UNIX TRUE64
Next Topic:	Move file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 24 07:56:47 CST 2025