Re: Puzzeling error with package

joel garry <joel-garry_at_home.com> writes:

> On Oct 30, 3:45 am, Tim X <t..._at_nospam.dev.null> wrote:
>> Hi All,
>>
>> today I ran into an 'interesting' problem and wanted to ask for
>> advice/feedback from others as I am having problems understanding how it
>> occured.
>>
>> The platform is Oracle 10.2.0.3 running on 64bit Linux in a RAC
>> configuration.
>>
>> I have a package which in the specification defines a constant i.e.
>>
>>   queue_name constant varchar2(15) := 'update_queue';
>>
>> This has been working fine for some weeks. today, I wanted to re-build
>> everything in the dev environment. I do this fairly regularly and prior
>> to moving the current code up to the testing environment. All code is
>> maintained under version control (svn).
>>
>> I have 3 scripts, the first drops all objects (tables, indexes,
>> sequences etc) and then re-creates them. the second script populates
>> various tables with default values adn the third script loads all the
>> packages. Packages are in two files - a spec file and the package body.
>>
>> All scripts spool to log files and I check each log file prior to
>> running the next script. There were no errors. However, when I tried to
>> run the first main procedure (it populates a queue with data), I got
>> errors. this was odd as none of this code has been changed in the last
>> couple of weeks (verified with svn) and it worked perfectly a week ago.
>>
>> Debugging the error, it turned out that the variable queue_name was
>> returning a value of NULL. this seemed very odd to me. How could the
>> variable be defined, yet have a null value when it was defined as a
>> constant and initialised to a non-null value?
>>
>> I reloaded and compiled the scripts e.g.
>>
>> @urs.pks
>> @urs.pkb
>>
>> No errors or warnings (I made sure the session was initialised to enable
>> all warnings. I checked the variable again e.g.
>>
>> exec dbms_output.put_line(nvl(urs.queue_name, 'is null'))
>>
>> I dumped out the source from user_source and it had the required line as
>> shown above.
>>
>> At this point, I'm really puzzled. I don't understand how the variable
>> could be defined, but not be initialised when the definition and
>> initialization all happen at the same point and no errors are
>> generated.
>>
>> I logged out of sqlplus, logged back in, loaded the package again from
>> source and all worked fine. Now I'm really puzzled.
>>
>> Experience has tought me that 99.9% of the time, problems like this are
>> generally 'carbon based' i.e. my mistake. However, this one has me
>> stumped and that frustrates the hell out of me. I expect I'm missing
>> some key bit of information and was hoping someone can shed some light
>> or at least point me in the direction of other things to look at. things
>> I've already decided to do should this problem raise its head again are
>>
>> 1. log in through a second session and see if I get the same problems.
>> 2..note which node I'm connected to (wish I had done this prior to
>> logging out!).
>> 3. Alert the DBAs just in case they can help prior to doing anything.
>>
>> Part of me thinks it may be a client issue (the client is using earlier
>> 10g libraries - can't remember what version) running on my linux
>> workstation. But this still doesn't quite gel as what I'm doing is
>> happening on the server and I verified that the sources in user_source
>> matched the files being loaded.
>>
>> so if any of you vastly more experienced Oracle bods can help enlighten
>> me, I would really appreciate it.
>>
>> One other bit of possibly relevant information (though unlikely to be of
>> any real relevance), my interaction with Oracle is via sqlplus running
>> as a sub-process from within emacs using sql-mode. I have to use this
>> interface because I'm a blind user and my main interface udner Linux is
>> through a package called emacspeak, that turns emacs into a speech
>> enabled interface. Therefore, I don't use any of the advanced GUI tools
>> that oracle now has and tend to still do things the 'old fashioned
>> way'. i get my information from querying the various v$ tables, writing
>> my own SQL and packages to do diagnosis (for example, I have my own
>> debug.show_errors procedure that prints out the error message and the
>> line before and after the error line as well as the error line as I find
>> this gives more context and makes it easier to fix errors that the show
>> errors command (I did check both, so I don't believe its a problem with
>> my scripts that is hiding the real cause or giving misleading info - but
>> can't rule it out. I've been using my packages since 8i with no
>> issues).
>>
>> any suggestions or corrections really welcome. I probably should mention
>> that I've never done any Oracle courses. I did a one week in-house
>> oracle SQL and PL/SQL course in 2001. For the rest, I'm self
>> tought. This means I could easily have developed some screwed up
>> conceptual models or just plainly have things screwed up re: how it all
>> hangs together. I am lucky to have two good DBAs and I think I've
>> developed a good relationship with them by ensuring I've done my
>> homework and tried to solve problems before dumping on them and I
>> attempt to put their advice into practice whenever possible. I believe
>> the bits I know I know fairly well, but I also know I've really only
>> covered about as much of the iceburg called Oracle that I could fit in
>> my glass of single malt (yep, I'm a heathen - its hot here and I think
>> the ice helps bring out the flavor!).
>>
>> thanks. Now where did I put that glass.........
>>
>> Tim
>>
>> --
>> tcross (at) rapttech dot com dot au
>
> Take this with much salt, since it is just off the cuff and I haven't
> thought it through, but you may be seeing some result of things being
> pinned in memory and not getting flushed or invalidated at the times
> you expect. I'm pretty sure I've seen that mentioned somewhere on
> metalink, perhaps even in bugs. Here's some more speculation FWIW:
> http://groups.google.com/group/comp.databases.oracle.server/msg/821f5410d92adad5?dmode=source
>

thanks Joel. Thats close to what the DBAs and I were beginning to arrive at. thanks for the reference. I'm going to look at metalink2 on the weekend.

Tim

-- 
tcross (at) rapttech dot com dot au