Re: Puzzeling error with package

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

jg

--
@home.com is bogus.
Athazagoraphobia – fear of being forgotten or ignored.