Stored Function/Trigger anomaly

From: Gregory Lechkun <_at_dteenergy.com>
Date: Fri, 09 Jul 1999 21:20:19 -0400
Message-ID: <37869FD2.F671BF02@dteenergy.com>

Hello everyone,

I had a weird problem on Friday (see the log entry). Apparently, a stored function (a simple one) would work with some parameters and not with others. After I re-compiled the function (and it was a "VALID" object) it would work. I had another problem earlier that day with a trigger which reported the same error (I don't know were the error was occurring because it started to work when I did the re-compiles of all objects). As mentioned in the log, I re-compiled all of USER's objects (procedures, functions, & packages) whether they needed it or not, and got my application up and running.

The reason why I bring this up is, did anyone see a simular occurance elsewhere? Is this a known bug?

Let me know what you think!
:
:
:

---

 09-jul-1999 2045/gpl
 SYSTEM: APPL
 SYSTEM_DOWN_TIME: 705 minutes
 SUBSYSTEM:
 SUBSYSTEM_DOWN_TIME:
 SUBJECT: APPL Extraction process failed

---

At 1615 hours, an user had reported that his data was not updating. After
looking at the problem, I found that none of the data was updating. I investigated the APPL extraction process and found that the process had
been reporting errors since 0815 hours, with the following error:

  Getting ORA Module Data Controls Error:   ORA-06502: PL/SQL: numeric of value error   ORA-06512: at line 1

Looking at the source code, if found that it was getting the error when executing the stored function get_MDC. I found that the function would work

when on get_MDC('DP_PERIOD') &  get_MDC('DP_HISTORY'), but not for
get_MDC('DB_SHUTDOWN'), get_MDC('DB_EXCEPTION'),
get_MDC('DP_FLUSH_PERIOD'),

and get_MDC('DP_DEAD_BAND'). There was no reason for some to work and some
not, so I tried recompiling the stored function (even though it was "VALID"),
and it started to work properly. Since I experienced a simular problem in the
creation of an account in the APPL account definition page, I decided to compile all
stored PROCEDURE's, FUNCTION's, PACKAGE's, PACKAGE BODY's. After shuting all
SQL*net connection down I re-compiled the objects. APPL was restored to

service at 2000 hours.
:
:
:

Regards,
Greg Lechkun
gpl :-)
DTE

Energy====================================================================

DTE     ////// ////////////       Gregory Lechkun -
DTE    //   //  //   //           Power Application Software Engineer
DTE   //   //  //   ////          lechkung<<nospam>>@detroitedison.com

DTE // // // //
DTE ////// // ////// Received on Fri Jul 09 1999 - 20:20:19 CDT