CODEWITHSUNDEEP
oracle-databaseunixplsql
Pravin Satav
Pravin Satav

Reputation: 702

Handling oracle procedure error in unix file

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

Upvotes: 1

Views: 1390

Answers (3)

dogbane
dogbane

Reputation: 274758

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;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 1

Peter &#197;
Peter &#197;

Reputation: 1319

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.

Upvotes: 0

Related Questions