Kevin Kempf's Blog

December 31, 2009


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:


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
 ,dbms_utility.format_error_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

Create a free website or blog at