Re: May procedures stops running for no reason!!! Please help

We were getting something similar. We have an billing application that was developed for us by another company (which is no longer doing work for us). This application has at least one package that calls another package that calls another package that then has a loop that is "entered" over 1,000 times.

The packages are supposed to mimic a state machine and so before the final package is started, a state is set. Then the package loops through this "state machine" and based on the value of the state, performs some action, then re-sets the state for the next loop iteration.

The actions that can be taken are usually calling procedures or  functions that will open cursors, fetch records, close cursors, insert records, update records
(you get the ugly picture...)

There was a problem with this package and to debug it, I tried using dbms_output. After several iterations of running the output even dbms_output failed to display anything on the screen.

I found the ONLY way to get dbms_output to show us was to:

       ALTER SYSTEM FLUSH SHARED_POOL;
EACH time prior to running the package.

Prior to this (and before looking at the code in detail), I had to decrease the OPEN_CURSORS in the init.ora so that this would not keep ending with ORA-3113 errors.

I am still not sure WHY this was occuring. At the time I just wanted to "make it work".

Anyhow, it is something to try!

--
Babette Turner-Underwood, TMI Communications
babettet_at_tmi.ca.NOSPAM (remove appropriate part to e-mail me)
Robert Augustyn wrote in message <70nemi$dmt$1_at_mail.pl.unisys.com>...


>No,

>It is not

>thanks

>robert

>mpir_at_compuserve.com wrote in message <70n9gn$o2e$1_at_nnrp1.dejanews.com>...

>>In article <70lme7$qm6$1_at_mail.pl.unisys.com>,

>>  "Robert Augustyn" <robert.augustyn_at_unisys.com> wrote

>>

>>I haven't run into this, but as an idea, is it possible you have

 overlapping


>>sessions where session 2 is locked out of something by session 1?  You

 said


>>that the program is run every few minutes.

>>

>>> Hi,

>>> I have stored procedure which is activated every few minutes from Unix

>>> shell.

>>> It is a package and is relatively large( over 2200 lines of code )

>>> When I start it runs for several hours and then I get:

>>>

>>> ERROR at line 1:

>>> ORA-04045: errors during recompilation/revalidation of

 ARADMIN.CALL_ACTIONS


>>> ORA-00933: SQL command not properly ended

>>> ORA-06508: PL/SQL: could not find program unit being called

>>> ORA-06512: at line 1

>>>

>>> and have to recompile the procedure and it runs again ok  for a while

>>> any idea what would be the problem and how to trace it?

>>>

>>> Thanks in advance for your help

>>> robert

>>>

>>>

>>

>>-----------== Posted via Deja News, The Discussion Network ==----------

>>http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

>

>