PL/SQL Unit Testing package

September 29, 2009

I could have sworn I’d posted this a long time ago, but I can’t find it here.

An Apex friend mentioned his interest in a simple unit test package for Oracle PL/SQL.  I shamelessly copied and simplified a unit-testing framework that Steven Feuerstein published years ago.  His package was quite good and offered lots more features.  But the installation was the antithesis of light-weight and it was a pain to support in our (somewhat unique) environment.  The commercial version he developed for Quest is that much and more.

I simplified Feuerstein original version so that:

  • It runs in our environment,
  • The unit test scripts are fairly readable,
  • It covered the basic unit tests I need for my work,
  • Minimal test suite set up,
  • Easily add more tests as the new function progressed
  • Easily comment out obsolete tests that aren’t supported later in the development,
  • Minimal overhead means the test scripts run fast,
  • The code_tester package is extensible.

Disadvantages from the commercial package that Quest sells (developed by Feuerstein):

  • It ain’t GUI
  • Scripts are not modular, so it can get tedious when they get very large.

The attached zip file contains 2 files:

  • The code_tester package itself (only 933 lines)
  • test code_tester.sql – a sample test script that shows the various features in use (53 lines)


  1. Compile the package,
  2. Run the test code_tester script in your Oracle development tool of choice.  (I’m a fan of Allround Automations’ PL/SQL Developer, but it works fine in SQL*plus.)
  3. Most of the tests are set up to succeed.  Change one so it will fail.  For example, change:'5 >= 5', 5, 5);

to:'5 >= 5', 5, 500);

It shows you which test(s) failed.  You can increase the amount of information reported, such as list the tests that passed as well as failed, count of failures, etc.

Output looks like:

FAILED    Test 'Value vs NULL, NULL NOT okay'  NULL NOT GREATER THAN 99999999
FAILED    Test 'Value vs NULL, NULL NOT okay'  NULL NOT GREATER THAN 29-SEP-2009 14:30:51
Total Successes: 19    Total Failures: 2
>  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
>  F        A  A     I   L      U     U R    R  E
>  F       A    A    I   L      U     U R     R E
>  F      A      A   I   L      U     U R     R E
>  FFFF   A      A   I   L      U     U RRRRRR  EEEE
>  F      AAAAAAAA   I   L      U     U R   R   E
>  F      A      A   I   L      U     U R    R  E
>  F      A      A   I   L       U   U  R     R E
>  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE

Though I haven’t enhanced this in a long time, I would welcome enhancements, feedback, suggestions, etc.  Obviously I have very little (to none) time to support this, but I’ll try to help where possible.

Download the zip file




Dang, I really meant to post regular updates while I was here at the Oracle Developer Tool User’s Group conference, but had too much fun going out to  New Orleans’ French Quarter.  Now I’m sitting in the airport waiting almost 2 hours for my flight because so many people were leaving at once that I had to share a cab to the airport, so left super-early.  So if this is more convoluted than usual, it’s because I got up at 5:15 and haven’t gotten enough sleep any night this week.  Forgive me (if anyone’s reading this, I rarely get comments).

I’ll try to get some more content in a later post if I don’t fall asleep here and now.
There were a huge number of Application Express sessions, which was great for learning, but I feel like they cut off the top of my head, peeled back my skull and poured information in.  Now it’s so full that they can’t close it back up!  And some of the speakers spoke so fast that my ears have been filled with their words by a firehose.  That’s even less-comfortable, especially when they were doing it.
My hat’s off to the ODTUG organizers on a great job.  I haven’t been to many conferences but this was easily the best in terms of organization, providing amenities for attendants, friendly service, good hotel, fabulous location and GREAT FOOD.
I already raved about the conference to one of the lovely organizers (whose name I didn’t catch) at the party last night.  I also gave a little constructive criticism about the problems with the quality of some of the speakers, which she acknowledged and clearly understood.  I joked about only getting 2 drink tickets and she immediately tried to give me another.  I declined because I’d already hit my limit (I’m a cheap date).  If anyone cares (or is even reading this), check out the podcast follow-up, where they briefly interviewed my co-worker and me about how we liked it.  I’m the one slurring his words after the second Chivas.  
Conference Party tips:
  1. I know we got drink tickets, but if you tip the bartender a buck for your drink, they’ll usually make it a tall one, a very good value IMHO!  But remember your limits, no puking on the conference organizer’s shoes!  (I didn’t, in case you’re wondering)
  2. On that vein, carry lots of singles around town for tips.  The service people work hard and really appreciate it, even if it’s just a buck, because you’re showing you care.  Tip freely and you’ll see a better time.  I tipped the street musicians and break-dance team I stopped to enjoy – pay for your entertainment.  On the other hand, don’t engage with the hucksters for the strip bars (ubiquitous on Bourbon Street) and the grifters and beggers.  Just say “No thanks” and keep walking.The party’s at the end of the conference and you’ve proven to the people who matter that you’re technically competent, so don’t be afraid to kick up your heels and do stuff that you wouldn’t normally.  At the beginning of the party when the band invited guys up to play the washboard, I was the bald guy on the left.  Nobody was going up when invited, but I had a ball!
  3. Make sure you check out all the food stations around the room, because they weren’t all the same and I almost didn’t see the shrimp (horrors!).
  4. Enjoy the noisy party, but check out the quiet corners too.  My co-worker and I stepped outside to the balcony to look at the city.  Even though it was the smoking lounge, we met up with a guy from Amsterdam who was fascinating to talk to.
Conferencing tips: (like I said, I don’t do conferences much, but maybe you don’t either and these will help)
  1. Even though we’re geeks, try to be more outgoing with conference attendees than you usually are.  Half the benefit of these things is networking, not just for that next job but for an answer to that tech question you need next month.
  2. Similarly, hand out your business card – Bring lots and give them away freely.  They’re paid for and you don’t get a credit for not using them when you need new ones.  I gave out a dozen and probably should have done more.  I was surprised more people didn’t have their cards with them.  Or maybe they didn’t want me to have their contact info???
  3. Don’t be late for the session starts (like I was yesterday).  I was amazed that most sessions started on time (though many went late).
  4. Get your butt out of the hotel and enjoy the local area.  We walked at least once a day and saw stuff that we would have missed.  We found some great jazz only because we kept looking and trying out different areas.  Though Todd from InstallFest told me this morning that he had even better luck by asking the concierge at the hotel, which I’ll use more next time.
  5. Read the conference brochure when you get here.  I missed a couple great opportunities because I didn’t.  One I would have loved was the walking photo tour of New Orleans that was right there in the brochure!  Aagh!
  6. Book the airport shuttle – I did on the way in but not the way back, which caused the abusively-early cab ride this morning.
  7. Take session notes – even if they’re just the highlights, it’ll help you remember later which whitepapers you should study later.
  8. Laptop users – If you like to take notes in sessions like I do, borrow an extra battery from your IT dept for the conference.  Mine hadn’t been holding a charge long before I came so I asked for a got a new battery plus a loaner second battery to replace my CD-ROM drive for the week.  Turn off your WiFi to extend the life too.  And charge it up during lunch in your room or between sessions.  I took comprehensive notes in every session, except for Tuesday when I forgot to charge up over lunch.

[More to come]

…  Monday

[Please excuse the weird formatting of this post.  I’m having compatibility troubles between EverNote (which I took notes with) and WordPress.]

Great conference for Apex Developers. Tons of sessions, many very good quality.  The following session just finished and unfortunately I wasn’t as impressed with the presentation of this one.  Though I’ll bore you with my notes anyway. (?)

Using the Apex Dictionary Views to increase the quality of your Apex Applications – Karen Van Hellemont, iAdvise
Read the rest of this entry »

You want to define a link between one page an another via a report column. Everything works great until you try a link where the source column value includes a comma, like:

Smith, Fred, A.

Then the link doesn’t work!

I found the answer in the Oracle Apex forum. This is documented in the 3.0 User Guide under itemValues as:

To pass a comma in an item value, enclose the characters
with backslashes. For example:

You need to put ‘\’ (backslash, not slash) on both sides of the column value you’re passing, simple as that. You don’t need to change the query on the receiving end at all.
Forum user Chet Justice even wrote up a sample query:

SELECT my_column_with_commas, –use this one to display
‘\’ || my_column_with_commas || ‘\’ my_commas –user this one for the link
FROM table_with_columns_of_commas;

Then change your column link to pass the my_commas value.

Piece of cake, once you know!

For some reason, if you want to substitute one of your application substitution strings into a page template, you need to use the following syntax:


I think this is odd, as the templates include all the standard substitution strings, such as


Go figure!

Problem: The other night, the Object Browser didn’t show all code from package body via FireFox.


In this Oracle forum post, someone suggested we check out version of PL/SQL Web Toolkit…

Did you install the latest PL/SQL Web Toolkit? The version must be or later. This is the version of the toolkit and not the database – Notes here .

They also suggested checking the DAD.

Did you configure the value of the character set in your DAD to be AL32UTF8 ?  See this.


I realized later that I didn’t have this problem in Internet Explorer. Score a point for Microsoft! 😦