Re: How do I retrieve an error status during sqlplus execution???

You need to make the following changes to your script.

somewhere before you do any sql or pl/sql, add the line: whenever sqlerror exit failure;

just before the closing END, add:

exit success;

Then, once sqlplus exits, look at the shell variable $? and it will have a 0 if you succeeded or a 1 if it fails.

Some programmers recommend that you use sql.sqlcode instead of 'failure'. The problem with this under unix is that the storage allotted to $? is not big enough to hold all of the possible value for sqlcode and as a result you'll get a truncated value that will be fairly meaningless. If you really need to know the actual sqlcode value then write a dbms_output.put_line(sqlerrm(sqlcode)) in your pl/sql or else redirect the output of sqlplus to a log file of some type.

Ken

"Laura González" wrote:

> Hi, again a newbie in this world. Thanks for your last help about
> feedback.
>
> Now my problem is this:
>
> I have several shell scripts with calls to sqlplus, similar to:
>
> echo "beginning"
> sqlplus user/pass << END
> update ....
> commit;
> exception
> when others then
> dbms_output.put_line ('You are in trouble')
> END
>
> echo "this is the end and it finished with status ..."
>
> Sometimes, it happens that a rollback segment is full, so I can see the
> message 'You are in trouble', I like it, BUT, and that is my problem, I
> don't know how could I retrieve an error status inside the shell script
> coming from the sqlplus.
> Now, if I ask for the status immediatly after de 'end' of may sqlplus, I
> always obtain OK, even in the case of the full rollback segment - I
> supose this is because sqlplus is ok, the problem was inside it -.
>
Received on Wed Jun 23 1999 - 09:50:34 CDT