Puzzeling error with package

From: Tim X <timx_at_nospam.dev.null>
Date: Thu, 30 Oct 2008 21:45:55 +1100
Message-ID: <87abcm4azg.fsf@lion.rapttech.com.au>

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!).
  2. 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
Received on Thu Oct 30 2008 - 05:45:55 CDT

Original text of this message