Re: end of infinite recursion in pl/sql?

Sorry for the errors in the package. Obviously it was meant to convey the idea of how the package is like, the real one I use was much more complex.

I appreciate the sharing your findings on the Windows-Platform. In my case where the package ran on a HP machine and where I do not have Sys Admin privileges, (though I could not see momory size growing constantly with the ps command), the server process eventully was terminated. I could not determine the immediate cause.

Anyway, I think a conclusion one might draw from this lession is even if recursion is really intended, one has to know the potential strains it puts on the system resources, right?

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b0kgj5$pucbg$1_at_ID-82536.news.dfncis.de>...
> > During development of a pl/sql package I bumped into an interesting
> > phenomena that should be familiar to those who had their lession in
> > other high level programming language. I coded a a package like this:
>
> [snipped code of a package that was not compilable but called itself
> recursively]
>
>
> > the associated server process entered an infinite loop. TOP shows its
> > is using close to 100% CPU time (it was on a multiprocessor machine).
> > Now the question is, if one does not get the DBA or Sys Admin to kill
> > the process, would it ever finish or respectively abort? Or would it
> > run forever until shutdown?
> >
> > In other programming language, afik each recursive call uses up a
> > chunk of memory on the stack and when the stack crashed into some
> > boundaries, the process will abend. However does it work on Oracle?
> > Where does Oracle create the stack and where could one find this stack
> > growing? Would it run into some boundary eventually?
>
>
> I ran your programm after correcting it on an Oracle 9.2 on windows
> and another session (where I was logged into as sysdba and queried
> dba_free_space, if that is important for this issue?) reported
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04030: out of process memory when trying to allocate 1952 bytes (top call
> heap,frame segment)
>
> And all sorts of other strange things happened: windows showed message boxes
> telling me that it was short of virtual memory and that it would assist
> me in finding unused files to be deleted, evertyhing got real slow (of
> course, swapping memory).
>
> So, my assumption is that Oracle does not use the Processor Stack, but manages
> an internal one. Don't even know if that is required by ADA from which PL/SQL
> derives.
>
>
> Rene Nyffenegger
Received on Tue Jan 28 2003 - 06:28:48 CST