Handling oracle procedure error in unix file

Question

In my application oracle procedure is currently called through unix script, when there is error in exception block we are handling something like this :-

PROC_LOGS('<PROC_NAME>', <TABLE_NAME>, 'Exception','Exception occured - '||SQLERRM);

PROC_LOG does nothing but inserts entry into log table but we need now to catch that error in unix also (return some value except 0) from where is it called so remaining process can be terminated best on that, what is the best way to do this?

CREATE OR REPLACE....
...
DECLARE
....
BEGIN
..
...
EXCEPTION
WHEN OTHERS THEN
PROC_LOGS('<PROC_NAME>', <TABLE_NAME>, 'EXCEPTION','EXCEPTION OCCURED - '||SQLERRM);
END;

Unix script part from where I m calling procedure

sqlplus <<-!
$US/$P@$I
set serveroutput on
@$SQL/execute_proc.sql $1 $2

execue_proc contains something like this :-

 define IN_1 = '&1';
  define IN_2 = '&2';

spool $SQL/test_&&IN
declare
 P_IN_TABLE_NAME varchar2(250) := '&&IN_TABLE_NAME';
 P_IN_REGION varchar2(250) := '&&IN_REGION';
begin
PROC_UPDATE_CHARGE_FACT(P_IN_TABLE_NAME,P_IN_REGION);
    end;

/
spool off

please post a snippet of your proc which shows the exception handling. — dogbane
– dogbane, Commented Dec 18, 2012 at 11:41

dogbane · Accepted Answer · 2012-12-18 12:14:34Z

1

You need to raise the exception, like this:

EXCEPTION
  WHEN OTHERS THEN
    PROC_LOGS('<PROC_NAME>', <TABLE_NAME>, 'EXCEPTION','EXCEPTION OCCURED - '||SQLERRM);
    RAISE;
END;

or use RAISE_APPLICATION_ERROR:

EXCEPTION
  WHEN OTHERS THEN
    PROC_LOGS('<PROC_NAME>', <TABLE_NAME>, 'EXCEPTION','EXCEPTION OCCURED - '||SQLERRM);
    RAISE_APPLICATION_ERROR (-20002, 'An unexpected exception occurred.');
END;

answered Dec 18, 2012 at 12:14

dogbane

276k77 gold badges407 silver badges415 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pravin Satav Over a year ago

I have added the unix script which is invoking oracle procedure script part in question.. Is there is any way to do this without touching oracle procedure part? preferably through unix script

dogbane Over a year ago

No, if your database proc is not raising the exception then I don't believe there is any way you can tell if it has failed on the client side (unless you query the log table which is not a good approach).

David Aldridge · Accepted Answer · 2012-12-18 12:09:25Z

1

You are presumably calling SQL*Plus, and SQL*Plus has a configuration item that should help.

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve052.htm#BACHCFEF

Since you probably execute the procedure from an anonymous block, the error should propagate to SQL*Plus and "WHENEVER SQLERROR EXIT SQL.SQLCODE" will return the relevant error code to the unix environment.

answered Dec 18, 2012 at 12:09

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

2 Comments

Pravin Satav Over a year ago

I have added the unix script which is invoking oracle procedure script part in question

David Aldridge Over a year ago

As @dogbane says,just raise the error after logging it with proc_log, and use this configuration of SQL*Plus to propagate the error back to unix

Peter Å · Accepted Answer · 2012-12-18 11:37:18Z

0

One way could be to have proc_log() to also write to a log file and then have the unix script to check the log file.

answered Dec 18, 2012 at 11:37

Peter Å

1,3191 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Handling oracle procedure error in unix file

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related