Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: pl/sql - lost variable value

Re: pl/sql - lost variable value

From: Vladimir M. Zakharychev <>
Date: Wed, 17 May 2006 13:01:16 +0400
Message-ID: <e4eoou$1a4q$>

"Alex" <> wrote in message
> 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) 
Received on Wed May 17 2006 - 04:01:16 CDT

Original text of this message