Re: Stored PL/SQL and corruption

In article <6ed1nl$jal$2_at_vnetnews.value.net>,   twod_at_not.valid wrote:
>
> stevec_at_zimmer.csufresno.edu wrote:
> : If your stored procedure uses the DBMS_SQL package, that is probably the
> : reason. The package here sometimes seems to get corrupted for one or more
> : sessions, and only after several days.
>
> The plot thickens:
>
> There is no dbms_sql used in the package;
>
> The error is seen when calling the package from a Pro*C program running on a
> NT box connecting via Net 8;
>
> A job run from a Unix box connecting via Sql*Net 2.3 works just fine, whilst
> the Net 8 process is having problems;
>
> It is not a consistent error : Say there are 3 files to read and import into
> the database by calling the stored PL/SQL. File 1 gives 6502 when the PL?SQL
> is called so we remove file 1 from the queue. Files 2 and 3 are imported
> without anything being done (ie no recompilation). File 1 is placed back in
> queue and fails with 6502 until the package is recompiled at which point it
> is imported with no problem.
>
> We have scoured the data for control characters and the like but there is
> nothing unusual.
>
> Oracle's suggestions, as ever, was to upgrade.

Until now, you didn't specify that it was a package that was choking. It may be suffering the same problem that Oracle's DBMS_SQL package has: After a period of time, the data stored by the package apparently gets corrupted, and from that point on, the package returns the ORA-6502: PL/SQL: numeric or value error.

I think it is an Oracle bug, and your experience leads me to think that the problem is in the way Oracle stores the data for public variables and cursors in the package that remain active between calls to the package. Someone else's solution was to try: alter system flush shared_pool;

My solution is to try: Alter package Your_Package compile;

                  and:  Alter package Your_Package compile body;

Since it is not the DBMS_SQL package you are having a problem with, you could create a stored procedure to call DBMS_SQL to do the Alter Package... and then automatically call it as soon as your call to your own package returned the error.

If you try this, please let me know if it works.

Steve Cosner

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Mar 16 1998 - 00:00:00 CST