Re: pl/sql - lost variable value

Alex wrote:
> 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?

You might be looking into a 10046 level 12 trace to see what is happening in bind variable substitution. See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6020271977738 (and search for "they were just null") and google for how to read trace files. Metalink Note:171647.1 is a good start if you have access.

>
> The application is a large client/server app that has been in
> production for over 12 years. I am new to the team and we basically
> work on support and bug fixes/minor enhancements. My Oracle knowledge
> is limited and certainly does not cover anything beyond ER and general
> PL/SQL and SQL skills.
>
> Any help you can offer would be fantastic. I am out of ideas.

You may also be hitting a bug, but hard to tell without a platform. show parameter cursor, anyways.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20060513/news_1mi13dismiss.html