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




This is the main map from their home page.  You can click on the icons under the text to see hover text, then click again to jump to that page, which is an additional concept map.  I like that you can sort of read them as English sentences, while still retaining the relationships of a diagram.

Main page of CmapTools site

Main page of CmapTools site

It looks pretty cool, though I’d think it would be slow to develop each map.  Much slower than a mindmap for example.  But I think having a tool to create these diagrams would be very helpful, especially when they get large and you want to be able to drill down into sub-concepts.

Update: April 16, 2009 – I found and fixed a bug in how it handled the contains clause in the filters.  Stupid me, I’d used contains in other flavors of SQL so never bothered to confirm that Oracle SQL supports it!

Sorry about that!!!

The purpose of this package is to grab the user’s current Filters and Searches and recreate them in a WHERE clause, so I could do some external reporting.  A couple other people have shown me their solutions to this but this is my original work (with a hint from Anton and Shunt).

The little bit extra mine adds that I haven’t seen elsewhere is that it supports User-Saved Reports.  So if a user clicks the report tab for one of their saved reports and possibly customizes it, my routine can figure that out and recreate their current query.

It also supports the user typing a value into the Search box, which Apex matches against all text and numeric fields currently displayed.  That was an amusing trick!  If you care, you can view the query that IR generates by using Debug mode.

What this code does not support is a sort order, since my external report tool has a fixed sort.  Shunt did something that works with sort orders that you should check out here:

If you compare how others handled the various filter options (IN, CONTAINS, etc) versus how I did it, I think you’ll prefer their method.  I know I did, and wished I could have figured it out when I was writing this!  :-/

From the IR report page (e.g. 2), you add the following Javascript to the Page HTML Header:

<script language="JavaScript" type="text/javascript">
function SaveAndRunReport() {
            'P7_IR_REPORT_ID:'+$v('apexir_report_id'), 1024, 768);

You can certainly make a simpler call, mine just happens to use a pop-up page.

In the target page,  (e.g. 7), you can display the WHERE clause by creating an item computed with the following SQL query:

SELECT apex_ir_query.ir_query_where(:APP_ID,
           :P7_IR_REPORT_ID) FROM DUAL;

Here’s the package.  It’s hosted on MediaFire, so you’ll have to live with the ads.

Let me know if you find it useful or have suggestions to offer.

– Stew

Is there some reason that my Parent tabs don’t display in the order of their sequence in the Development environment?

For example, here’s what the users see with the sequences:

Tabs as displayed in my application

But here’s what I see in the Development page.

Tabs in Development

I find this pretty confusing, anyone else?

I realize that you can have multiple tab sets (which confuses me even further at times!) but these are all in the same tab set.

Apex Training days

November 12, 2008

We’re bringing an Apex trainer to campus for the next 3 days to get everybody (15 of us including a few managers auditing?) rolling with their Intro to Apex.

Anton from Concept 2 Consulting is coming up from the Boston area.  We’ve talked with him enough already that he should know pretty well what we’re after.  I’ll be sitting in the back listening for gems and connecting information that I haven’t already learned and trying not to act like a know-it-all!

Really, I’ll try.  But I’m currently developing on my fourth Apex application and working my way through John Scott and Scott Spendolini’s mid-level book “Pro Oracle Application Express” (which is pretty good so far).

I was playing with MindManager 8 for a few minutes last night and got it to browse and extract data from my Oracle database.  That was pretty slick, though there’s almost no documentation for it, so I was just bumbling around.

Browse got it to list the tables and their columns.  I couldn’t figure how to get it just show the rows I wanted, but it would extract column values and create them as custom properties in new topics or even a new map.  I don’t get it by a long shot but the promise is there.   The Custom Properties feature of MM has always been somewhat of a mystery/kludge to me, you define your own properties, but there’s no easy way to populate them (until this database functionality works?) .  I spent a couple hours messing with them a year ago but never saw the point.

But I’m still jazzed that I got my 2 favorite technologies to talk to eachother, even if they don’t completely understand eachother!  :-/

For example, when MM8 tried to read the list of tables using the Configure Databse Connection, it threw a lot of exceptions, I think because it was trying to read the system tables that my account doesn’t have permissions for.  That was pretty ugly!  But then I could identify tables it shouldn’t try to list, so hopefully I won’t have to go through that again.

And if I try to add a query clause in the provided field “Enter text to find”, it throws 2 exceptions saying “Illegal variable/number”.  So I’m thinking they need to get a bug release for this out pretty soon.  [sigh]

MM8 logo

MM8 logo

Sorry to those of you who subscribe to this for Apex content, but this is too good to keep under wraps.  Actually, if you want a good tool for designing, documenting and organizing information for your Apex applications, check out MindManager.  Try out the Free Trial version.

MindJet released their newest and greatest version of MindManager today and I’m pretty impressed.

I’d participated in their beta test and didn’t see much that was new and notable.  But what they actually released is pretty cool.

Share your map with anyone

Want to share your map but your buddy doesn’t have MindManager and has zero interest in downloading the free viewer?  No problem!

Just export the file as a pdf and Adobe Reader will start up the Flash player for your map.  So your friend can browse your map interactively, expanding and contracting the branches, zooming, etc.  I think that’s the slickest thing!

You can also export as an Adobe Flash (SWF) file that can be viewed via a web browser, so you can post it to your blog.

Here’s a small example of an Apex Knowledge Base I started (and never finished) a while back.  Obviously you’ll need the free Adobe Flash player installed in your browser.

They’ve also further integrated their Mindjet Connect tool, which lets you share and edit maps and any Microsoft Office-type document on their servers with your closest friends.  I think their price for this service is out of reach for most, but more power to them.

Your maps on the web!

I haven’t learned much about this (missed much of yesterday’s webinar), but they say you can access your files via their MindManager Web utility.  No clue how that’s different from Mindjet Connect, sorry.

Connect to your Database

Yes, they had very basic access to SQLServer and Oracle, if you wanted to roll your own connection.  I’m much more excited about this, hoping against hope that it will be fairly easy to implement this.  Databases they say they support include:

  • Access
  • SQLServer
  • MySQL
  • Oracle
  • IBM DB2
  • Excel
  • Text files (*.txt and *.csv)

Integrating Web Search to your maps

This sounds pretty slick too.  Basically you define a search against your favorite search engine (from their list) and then the results appear in your map.  Refresh them on demand.

I’m really looking forward to digging into this functionality.  Expect more later.