Re: Puzzeling error with package

sybrandb <sybrandb_at_gmail.com> writes:

> On 30 okt, 11:45, 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
>
> OK,
>
> I've read this all in full.
> It basically boils down to 'It doesn't work as expected'. You state
> you got errors. You don't state which errors.
> I waived my magic wand, and just like in other computer adventures
> 'Nothing happened'.

sybrandb, you obviously have a lot of Oracle knowledge and I respect your expertise in this area, but I'm not convinced regarding your comprehension skills!

I didn't give specific error messages because I know what the error is due to. The error was caused because a variable was returning a null value. My question is that I don't understand how it could have a null value.

> As far as I know variables, even if they have been defined globally in
> a package spec, can't be accessed directly in a select statement.
> You need to set up a dummy function to get them.
> I doubt this has changed with Oracle 10g.
>

there is no SQL involved. I guess I should have explicitly said this is occuring in a plsql procedure, but It hought that was obvious. I also pointed out how I tested the variable e.g.

exec dbms_output.put_line(nvl(urs.queue_name, 'is null'))

I also tested the variable in the procedure using an assert package I've written. Essentially it tests the variable passed to see if it is null and throws an exception, which it does.

to be more precise

The error I was getting was about a missing identifier and was thrown when my plsql procedure to add records to an Oracle queue failed. I tracked down the problem and it was because the variable with the name of the queue to write the data to was null.

This is where it gets confusing. The variable that specifies the queue name is defined in the spec part of a package and is defined as

queue_name constant varchar2(15) := 'update_queue';

I put some debugging code in the procedure that was failing that showed the value of this variable was NULL.

My confusion is that I don't understand how the variable can be defined but its value is NULL when it is defined as a constant and initialised all in the same line of code.

You were right in your statement that it "boils down to 'It doesn't work as expected". I'm quite willing to accept that this is due to my expectations being out of line with how it really works, but I'm stumped with respect to what more I can do to either rectify my understanding or understand why it happened. I don't expect anyone can give a definitive answer, but maybe give me some pointers regarding what to look for, things to check etc. for all I know, it could just have been sun spots and will never occur again!

regards,

Tim

-- 
tcross (at) rapttech dot com dot au