Re: catching exceptions in Pro*C thrown by stored procedures

From: Johan Wegener <xjw_at_xdde.xdk>
Date: Thu, 19 Aug 1999 10:56:14 +0200
Message-ID: <7pggtu$f4p$1@news101.telia.com>

paul cluiss <paul_cluiss_at_intervoice.com> skrev i en nyhedsmeddelelse:05546297E05B855C.6CD7AD0D27412518.9A83DEFEF653D9B4_at_lp.airne ws.net...
> I have a Pro*C program which calls a stored procedure. Everything works
> great, but here's my problem: My stored procedure catches input data
> errors and throws exceptions, which I would like to catch inside my
> Pro*C program. Has anyone done this before? I've examined the Pro*C
> samples, and I found a few Pro*C programs which catch their own
> exceptions, but so far I've not found any examples of Pro*C programs
> which catch exceptions thrown from a stored procedure.

The basic problem is that PL/SQL exceptions does not automatically propagate into C++ exceptions (if you use C++). Thus you cannot catch exceptions thrown by PL/SQL using the C++ catch functionality. If you want to, you must map the exceptions yourself.

For "normal" C this is the approach I normally use - it is quite simple actually:

Every stored procedure/package has got an outer "WHEN OTHERS" exception block that simply maps the thrown exception to the corresponding Oracle return code. All procedures/functions should return this code, either through an OUT parameter or as the function return code:

• ******************************** function bla return number is ... begin ... exception when others then return sqlcode; end;
• ********************************

In your Pro*C program do as follows:

/********************************/

..
#include <sqlca.h>
...
EXEC SQL BEGIN DECLARE SECTION;
long l_oraclecode; // Oracle return code EXEC SQL BEGIN DECLARE SECTION;
..
EXEC SQL EXECUTE
BEGIN
     :l_oraclecode:= bla; -- Exceptions thrown inside the function are "caught" here

    EXCEPTION
    WHEN OTHERS THEN
        :l_oraclecode:= SQLCODE; -- Exceptions thrown due to an error calling the function are caught here

    END;
END-EXEC; if (sqlca.sqlcode != 0L || l_oraclecode != 0L) then {
...

    /* Error handling and "exception" handling */ }
...

/********************************/

Hope this helps...

Cheers,
Johan Received on Thu Aug 19 1999 - 03:56:14 CDT