Interesting DBM_SQL/Pro*C error

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 Received on Wed Dec 23 1998 - 03:34:57 CST