Home » SQL & PL/SQL » SQL & PL/SQL » Handling error in Multiple Procs.........
Handling error in Multiple Procs......... [message #244340] Tue, 12 June 2007 07:07 Go to next message
mvnrajan
Messages: 5
Registered: June 2007
Junior Member
Hai All...

I am struck with a problem for a while... Will you please give some suggestions to come out of this.....

I have 2 procedures.
1. PROC_MAIN
2. PROC_SEC

PROC MAIN is the procedure where am handling exception portion. if exception happend, i have to update STATUS table with last processed record.PROC_SEC is called from PROC MAIN.

PROC_SEC is doing some record transactions.In case, error happend in this procedure, i have to send the last processed record to PROC_MAIN procedure which updates STATUS table.

Problem is am not able to send the last processed record from PROC_SEC if exception happend. I tried to do this with OUT parameter in PROC_SEC. but when error happen, its not assigining values to OUT parameter.

Any suggestions invited...

Re: Handling error in Multiple Procs......... [message #244345 is a reply to message #244340] Tue, 12 June 2007 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a package variable.
SQL> create or replace package pkg 
  2  as
  3    myvar integer;
  4  end;
  5  /

Package created.

SQL> create or replace procedure p1
  2  is
  3    i integer := 0;
  4  begin
  5    dbms_output.put_line('doing record 1');
  6    pkg.myvar := 1;
  7    dbms_output.put_line('record 1 done');
  8    dbms_output.put_line('doing record 2');
  9    pkg.myvar := 2;
 10    dbms_output.put_line('record 2 done');
 11    dbms_output.put_line('doing record 3');
 12  
 13    -- generating an error
 14    i := 1 / i;
 15  
 16    pkg.myvar := 3;
 17    dbms_output.put_line('record 3 done');
 18  end;
 19  /

Procedure created.

SQL> create or replace procedure p2
  2  is
  3  begin
  4    p1;
  5  exception
  6    when others then 
  7      dbms_output.put_line('*** ERROR: '||sqlerrm);
  8      dbms_output.put_line('>>>> last record done: '||pkg.myvar||' <<<<<');
  9      raise;
 10  end;
 11  /

Procedure created.

SQL> exec p2;
doing record 1
record 1 done
doing record 2
record 2 done
doing record 3
*** ERROR: ORA-01476: divisor is equal to zero
>>>> last record done: 2 <<<<<
BEGIN p2; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MICHEL.P2", line 9
ORA-06512: at line 1

Regards
Michel
Re: Handling error in Multiple Procs......... [message #244351 is a reply to message #244345] Tue, 12 June 2007 07:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You COULD do it using OUT NOCOPY.
It could have some side-effects, but the OUT will not be cleaned in case of an exception
Re: Handling error in Multiple Procs......... [message #244463 is a reply to message #244345] Wed, 13 June 2007 00:45 Go to previous messageGo to next message
mvnrajan
Messages: 5
Registered: June 2007
Junior Member
Hi Michel,

Thanks for your Immediate response. As you suggested, its working fine.

But as per my project requirement, i have to run procedure PROC_MAIN in multiple instance. Same time, Maximum 10 PROC_MAIN will be running. In this case, Global variable, concept will fail. All the ten instances will be using the same variable.

Any other way to achieve this...

I cannot make my procedure as package...

Thanks and regards
Natarajan.
Re: Handling error in Multiple Procs......... [message #244465 is a reply to message #244463] Wed, 13 June 2007 00:52 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Package variables are private to the session.
Each session has its own instance of the variable.
So this works in multi-sessions environment.

Regards
Michel
Previous Topic: update statement
Next Topic: center allignment in the select query
Goto Forum:
  


Current Time: Tue Dec 06 14:12:57 CST 2016

Total time taken to generate the page: 0.05884 seconds