"Alex" <adurrant_at_hatch.ca> wrote in message
news:1147806547.048536.124280_at_g10g2000cwb.googlegroups.com...
> I'm having a problem that I'm hoping someone out there has experienced
> before and has a solution for.
>
> I have a package that includes a function that is executed on an insert
> trigger. About 2/3 of the way through the function, the values of 3
> variables are lost. i.e. all 3 variables "go to" null for no appparent
> reason. I checked, and there are no modifications being made to the
> variables. Also, I created a seperate package for the variables to
> access them there instead, they still "go to" null. So the procedure
> parameters, the local variables and the variables in a "repository"
> package all lose their values and end up being null. To furhter
> complicate the situation, this only happens when the process is run
> from a cursor. i.e. cursor of x number of parent records, results in x
> inserts into a table. After y inserts, (and y is always a different
> number after each attempt) the variables lose their values. (Hope that
> was understandable). Has anyone encountered this before? Is this a
> memory/tuning issue? What should I ask the DBA to look into?
>
Can be anything from obscure code path that eventually fires
and nulls these variables (an exception maybe,) to a bug in
PL/SQL engine. Without knowing Oracle version and seeing
the code and typical usage it's impossible to diagnose. However,
if you can refactor the function and decompose it into several
shorter functions/procedures that are easy to understand, you
may spot the place where this happens. If you have any
exception handlers for OTHERS in the function, you may want
to disable them as they may be hiding the problem.
--
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Wed May 17 2006 - 04:01:16 CDT
