Re: Problem with PL/SQL stored procedure

Ramya wrote:

edited ...

...Hello,
If for some reason,
an exception is caught in the stored procedure (like no_data_found exception), then the following problem occurs. If we try to trigger the stored procedure again through the VC++ executable, the variables in the stored procedures have the values as in the previous execution.
=== end orig text =====

    I've had this problem - and it had to do with the binding of the VC++ vars into the stored procedure.

    What I did :

        My stored procedures have an intensive internal error checking code to detect potential error conditions BEFORE the acutal error is invoked. For example - a null source querry or a PK volation on an insert task.

    Then , the stored proceddure has a success/fail indicator that reutrns to the calling finction a indication of sucess or the type of error condition encountered without raising an application-level error.

    I interpret the return value ( OUT varriable specification ) and process accordingly. In this manner I don't enter the black hole of bindry management that I believe to be compiler specific rather than a problem on the oracle side. ( PLSQL failures calling the same proceedure with the unfixed error templates did not result in this 'shadow' effect ... ).

    Anyway - it solved my problems. Don't let the fail condition in the package procedure make life hard. They're written to be useful well-formed utility code so handle the objectionable protions programatically rather than this nasty 'shadow' persistence.

    Sorry - best 've found. I'll watch this thread hoping for better...

jds

>
Received on Tue Jun 26 2001 - 21:16:25 CDT