Re: Puzzeling error with package
- From: sybrandb <sybrandb@xxxxxxxxx>
- Date: Thu, 30 Oct 2008 08:30:41 -0700 (PDT)
On 30 okt, 11:45, Tim X <t...@xxxxxxxxxxxxxxx> 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'.
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.
--
Sybrand Bakker
Senior Oracle DBA
.
- Follow-Ups:
- Re: Puzzeling error with package
- From: Tim X
- Re: Puzzeling error with package
- References:
- Puzzeling error with package
- From: Tim X
- Puzzeling error with package
- Prev by Date: Re: set_session_longops in perl
- Next by Date: Re: RH Server puking when RMAN is writing to NFS and NFS goes down
- Previous by thread: Puzzeling error with package
- Next by thread: Re: Puzzeling error with package
- Index(es):
Relevant Pages
|
