Kevin Kempf's Blog

December 31, 2009

dbms_session.reset_package

Filed under: 11g, PL/SQL — kkempf @ 11:51 am

Intermec 3400e

At a time which normally includes trips to the break room for Christmas cookies, half the staff being out on any given day and light traffic on the commute, I found myself exceptionally busy this week.  We’re in the midst of a regression cycle, and I’d revamped a mission critical PL/SQL package (4700 lines, to be exact!) which generates product and shipping labels (onto Intermec 3400e printers) for us in the manufacturing process.

Why did I have to rewrite it, and why now?  It turns out our labeling solution, Optio (commercial software which writes to label printers) is all but dead from a support standpoint.  In shopping new vendors, we were unimpressed and figured we could use an existing product we owned (Bartender).  In looking at the existing PL/SQL, I believe every rule of good programming was violated.  It’s like a case study in how to write unmaintainable code.  Seriously, not to harp on consultants writing terrible code, but were you even aware that PL/SQL supported goto syntax?  So effectively, we’re required to regression test this code as if it were new.

As it turns out, I had some logic bugs in the rewritten code (not a big surprise) which required a little attention; after fixing the issue, when called from a trigger, the code would always return ORA-6508 (PL/SQL: could not find program unit being called) and ORA-4068 (existing state of packages discarded).  I fought this issue for quite some time, before a real programmer suggested I might try:

DBMS_SESSION.RESET_PACKAGE;

Wow it worked like a charm.  I attribute this to my use of global variables, of which there are numerous documented notes and references (Tom Kyte even calls them evil!)  After I dumped them things worked, more or less, as expected, and I was able to resume holiday pace…

Incidentally, in the course of running down this error, using the exception block of PL/SQL, I found this nifty bit of code which is now my new standard for error handling (in this case, writing to an error table):

insert into error_log(error_date
 ,module
 ,code_location
 ,error_code
 ,error_message
 ,description
 )
 values
 (sysdate
 ,v_module
 ,null
 ,p_error_code
 ,p_sqlerrm
 ,dbms_utility.format_error_stack || ' ' ||
 dbms_utility.format_call_stack
 )
 ;

What I’m really raving about here is the dbms_utility call.  It made it crystal clear what my error was, and if you’re not familiar with it I suggest you try it:

dbms_utility.format_error_stack || ' ' || dbms_utility.format_call_stack
Advertisements

Create a free website or blog at WordPress.com.