Re: DBMS_SQL accumulate error: ORA-06502

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/12/16
Message-ID: <882296992.1596063747_at_dejanews.com>#1/1


Finally! Somebody else with the same problem. I was afraid the problem only occurred here.

What system do you have, and what version of the database are you running? Ours is Oracle7 Server Release 7.3.2.2.0 on an HP 9000 (Unix) system. The problem pops up occasionally at the same place: the DBMS_SQL.PARSE command issued by the QA utility form. The error is as you say: ORA-06502: PL/SQL numeric or value error.

I have seen the problem go away after several hours on its own, too. Usually, only one session encounters the problem, and then all sessions following have the problem as well. One time, 3 users experienced the problem at about the same time.

But we have come up with a little better solution than bouncing the server: The DBA logs into the SYS account and issues two commands: Alter package DBMS_SQL compile; and Alter package DBMS_SQL compile body;

Once the above commands are done, the problem goes away for a day or so.

The problem has happened here often enough that I placed an alert in the QA utility form telling users what to do when it happens.

Regards, Steve Cosner http://members.aol.com/stevec5088 -- downloadable QA utility form: dynamic display and update access to any table.

In article <34969F45.EA079132_at_dd.ru>,   "Valery A. Sorokin" <vsorokin_at_dd.ru> wrote:
>
> This is a multi-part message in MIME format.
> --------------05BF88F59715ABE63EFC4153
> Content-Type: text/plain; charset=koi8-r
> Content-Transfer-Encoding: 8bit
>
> Dear Oracle’s Gurus:
>
> We often use dynamic SQL (DBMS_SQL package)
> in our Dev2K applications.
>
> We find out the strange thing: from time to time
> (usualy in several days after restarting server)
> the Error 'ORA-06502: PL/SQL numeric or value error'
> appears during runtime.
>
> After restarting server the error disappears for
> another several days. Then it appears again.
>
> Under Forms debugger the error occures on the command
> DBMS_SQL.PARSE(...)
>
> What could it be ?
>
> Thanks in advance. Please mailto:vsorokin_at_dd.ru
>
> --
> Valery A. Sorokin
> ProSoft Ltd., Russia

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Tue Dec 16 1997 - 00:00:00 CET

Original text of this message