PL/SQL Unit Testing package

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)

Usage

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:

code_tester.ge('5 >= 5', 5, 5);

to:

code_tester.ge('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

Enjoy,

Stew

ODTUG Wrap-up – Part I

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]

Using Apex Dictionary Views… Notes from ODTUG Conference

…  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

Apex – Column Linking with data that includes commas

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:
\123,45\

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!

Apex – Substitutions into page templates

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:

&EXTRA_BODY_DEF.

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

#WORKSPACE_IMAGES#

Go figure!

Apex – referencing Workspace images

1. Upload your file to the Workspace.

2) If the file is application-specific, assign it to the application.

3) Note the image name in the Shared Components->Files->Images. You’ll need to click on the icon/link for this item and get the image name value from the detail page, because the icon list replaces underscores with spaces and you need the exact name.

4) To reference it, like your company logo that goes on all pages, add something like the following on the Application Definition page in the Logo field:

#WORKSPACE_IMAGES#company_logo.jpg

You can also use this to reference Cascading Style Sheets that you’ve uploaded to your application.

Apex Issues – Object Browser doesn’t give all code?

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

Suggestion:

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 10.1.2.0.6 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.

Finally:

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

Apex Authorization Schemes – PL/SQL Function returning Boolean

Personally, I’ve found the authentication and authorization stuff a little tricky and have spent way too much time trying stuff out.

I was trying to use our college’s single-sign-on web authentication method, but never quite got it working.  I found out last week that an Oracle programmer who’s smarter than me figured it out!  But I still find the Oracle Database Account method to be worth using because:

1. It’s already been figured out by the pros!
2. Most everyone that would use one of my applications already has an Oracle account.
3. I’m not that big a fan of reinventing the wheel!

Having chosen the Database Account Authentication method, here are my notes on creating the simplest authorization scheme possible.

ApexGen – The Oracle APEX Generator

A utility to generate Oracle Application Express pages based on a PL/SQL API.

SourceForge site: ApexGen

Makes some assumptions about your setup, much of which doesn’t apply at our site.

Apex Database Objects

1. Workspace files: apex_workspace_files

includes columns for:

file_name – name of file uploaded

file_id – unique identifier

application_id – application number

workspace_name – should always be your workspace

mime_type – e.g. text/plain, image/gif, application/excel

file_size – file size in bytes

created_by – e.g. APEX_PUBLIC_USER

created_on – sysdate

document – blob that contains contents of uploaded file

2. Apex access log: apex_workspace_access_log

Creates log record every time you log into application via Apex standard login page (I think).  Must be workspace and Apex-owner schema-specific?

Columns include:

ip_address

seconds_ago – how log ago the access happened – dynamic?

access_date

authentication_result – e.g. AUTH_SUCCESS

custom_status_text – ?

3. Apex summary log: apex_workspace_log_summary

Includes columns:

application_id

page_views

distinct_pages

total_elapsed_time

first_view – date/time

last_view – date/time

period_in_days

4. Application activity log: apex_workspace_activity_log

Logs internal (Apex) as well as pages accessed in your application.

Columns include:

application_id

application_name

application_schema_owner – e.g. STEW_STRYKER

page_id

page_name – e.g. Load Import File – new

ip_address

error_message – e.g. ORA-00904: “ADDRESS_LINE_2_BILLING”: invalid identifier

error_on_component_type – report

error_on_component_name – Review Participants

5. Apex view definitions: apex_dictionary

Lists all views and their columns.  The view columns are documented in this table in the Comments column.

To get a list of Apex Views

SELECT DISTINCT apex_view_name FROM apex_dictionary;

6. Installation-specific?

V3 Apex schema: flows_030000