Re: Interesting DBM_SQL/Pro*C error

On Wed, 23 Dec 1998 09:34:57 GMT, geoliver_at_erols.com wrote:

>I have an application that uses Pro*C to invoke a stored procedure that
>uses DBMS_SQL. The stored procedure (let's call it DISPATCHER) uses
>DBMS_SQL to PARSE, BIND_VARIABLEs, and EXECUTE other stored procedures
>that are determined by a lookup in a DB table. Everything works just great
>until I re-compile one of the stored procedures that is
>EXECUTEd by DISPATCHER (with a "CREATE OR REPLACE PACKAGE
>BODY ..."). If try to execute the re-compiled stored procedure via
>DISPATCHER by way of the Pro*C interface I get an error:
> "ORA-06502: PL/SQL: numeric or value error ..."
>
>NOTE: the Pro*C application had an established session with the DB when
>the stored procedure was re-compiled. The re-compiled stored procedure is
>contained within a PACKAGE.
>
>If I invoke the re-compiled stored procedure via DISPATCHER by way of an
>interactive SQLPlus session it runs without a problem.
>
>If I stop my Pro*C application and restart (establish a new session) and
>then invoke the re-compiled stored procedure it runs without a problem.
>
>I can get around the problem by making sure that the Pro*C application
>is not running when re-compiling the stored procedure, however, I'd like
>to identify the real problem.
>
>Any information would be appreciated.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hello,

The real problem here is the open session combined with nested procedure calls and incomplete error forwarding.

If you recompile a package that is being used in an other session this leads to an oracle error "existing state of package has been discarded" or some such in the session running DISPATCHER.

If the error section in DISPATCHER does not take this errorpossibilty into account ( ... WHEN OTHERS THEN NULL; for instance) the returnvalues in the bound variables are undefined and lead to the errormessage that that returnvalue could not be handled instead of giving the true cause.

--
Henk de Wilde

     Being crazy just means you're having original delusions. Received on Sun Dec 27 1998 - 08:38:06 CST