Signal in a procedure and catch in another

[Former Member]

Hi everyone,

I'm struggling a bit with signalling.

I have a procedure that calls two other procedures. Lets call them Procedure Main, that calls Procedure 1 and Procedure 2.

Now, what I need is to signal an exception from Procedure 1 to Procedure Main.

What's happening is that, even though I have a declare exit handler for SQLEXCEPTION in Procedure Main, when I signal the exception in Procedure 1, it's going to blow up to process and not execute Procedure 2.

Is there some way to achieve this?

Thank you,

Fábio

[anindya_bose · Accepted solution]

This is how my main procedure is defined. 

[Former Member · Accepted solution]

Hi Anindya,

That's not exactly what I really wanted, but it works.

Thanks,

Fábio

[anindya_bose]

Hi Fábio

One way probably you could do this by handling exception in your proc1 as out put parameter.

This is what I did..

First time if I call this procedure, it would give me 'inserted successfully' message at out put .

But, if I call this second time, it should give me Error Code as output. 

You just need to catch this Error Code in calling procedure ( main one ) and then process a IF-THEN-ELSE block. .

First Time Call:

Second call gives me out put 301 , which is the error code for Unique Key Constraint violation .

Hope this helps

Cheers

Anindya