Dear Oracle technologists,

Please accept my apologies for not sending you a January newsletter. I was busy busy busy...

• Adding to my family: we now have a foster-son, Sean. He is 16 years old and a truly wonderful boy. We are greatly enjoying our time with him, but of course it does take some time to adjust. Our home had turned into an empty nest when our youngest, Eli (19), moved into his own apartment while he attends Depaul University, studies guitar, plays in a band, and does all the other stuff that 19 year olds do (and fathers probably don't want to work too hard at remembering, to avoid both jealousy and worry). So now we need to actually plan meals, pay attention to school calendars, and so on. Fortunately, Sean is also very athletic, so he and I have been playing racquetball, tennis and pool, climbing pseudo-rock walls, going bowling, and more. In other words, keeping me from going soft and keeping me humble!

• Putting out the first pre-production release of Qute, the Quick Unit Test Engine. More on that below.

But I am back, it is 2006, and I would like to share with you what I have been doing with PL/SQL lately. By the way, I would also love to hear what you are doing with PL/SQL, so if you have any interestings stories about cool code you have written, funny experiences with the language, etc., please do get in touch.

Qute, the Quick Unit Test Engine

As I am sure some of you know, several years ago I created utPLSQL, the unit test framework for PL/SQL programs. utPLSQL is an open source project and consists of a number of backend Oracle objects (tables and packages, primarily) that help you standardize the way you write unit tests for your code, and then run those tests, automatically giving you the results. I believe that utPLSQL was a major contribution to the world of PL/SQL development -- but it didn't really catch on, in terms of tens of thousands (or more!) of developers actually using it. Bummer.

Why are so few people using utPLSQL? I am convinced that PL/SQL developers do very much want to test their code and get rid of bugs. They just have so little time. If they/we are going to test, it has to be easy and quick. And utPLSQL simply doesn't meet those requirements - you have to learn the test API and write a bunch of code. Bummer.

So I went back to the drawing board -- and came up with Qute. In the testing world of Qute, you don't write test code (or at least, not very much!). Instead, you define your test through a graphical interface, thereby building up a test repository. Then Qute will generate the test code and run it automatically for you.

I encourage you to check out Qute by downloading and installing the software. Qute will at least initially be free of charge, but I offer no guarantees for the future. Of course, if I ever do start charging for Qute and that turns you off, you can just take your latest set of generated test packages and go off on your own merry way!

Disclaimer: Qute is not yet in production. You will encounter bugs and limitations. Furthmore, Qute is far from fully implementing my vision of 100% declarative specification of unit test definitions (that is, no coding needed to test your own programs). Still, I am confident that you will find it to be by far the best path to designing and running tests of your PL/SQL code.

If you have ideas for enhancements or you need to notify us of a bug, you can choose any of these options:

1. Visit http://www.unit-test.com/contact.php and fill in the form.
2. Register at the Qute forum, http://www.unit-test.com/phpBB2, and then enter your idea there.
3. Send an email to support@unit-test.com.

Introduction to Qute
http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370868&src=app/arc_pub&action=pb

Using Qute to test PL/SQL collections
http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370887&src=app/arc_pub&action=pb

Using Qute to test cursor variables
http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370920&src=app/arc_pub&action=pb

Upcoming training opportunities

The next major opportunity for PL/SQL developers to receive training, from myself and others, is through the Desktop Conference 2006 virtual conference organized by ODTUG. I encourage you to sign up and take advantage of attending this conference from the comfort of your own cubicle.

My presentation at Desktop Conference 2006 is titled "SQL Games We Can Play in PL/SQL" and comes with this abstract:

"No doubt about it: PL/SQL is the best place to write SQL when you need to access the Oracle database. No need for JDBC, ODBC or any of that other nonsense! Just write your SQL and off you go...but wait, it gets even better! Over the past several versions, Oracle has added SQL-centric functionality to PL/SQL to make it easier to write and faster to run SQL from within PL/SQL programs. This presentation will make sure you know about all the things you can do to take maximum advantage of SQL inside PL/SQL, from BULK COLLECT and FORALL to table functions, with a visit into the world of dynamic SQL."

I will also be in Florida on February 24th to do a 21st Century PL/SQL training for the Oracle User Groups in the western part of that wonderfully warm state. Click here for more information.

Tip(s) of the Month

For this newsletter, I will draw on tips I have provided on the Oracle Technology Network Best Practice PL/SQL page.

Calculating amount of time between two dates

The best way to calculate the amount of time between two dates is to take advantage of the INTERVAL and TIMESTAMP datatypes, introduced in the Oracle9i Database. The following function takes advantage of these datatypes to accept two dates and return the interval of time between them:

CREATE OR REPLACE FUNCTION date_diff (
 start_date_in IN DATE
 , end_date_in IN DATE
)
 RETURN INTERVAL DAY TO SECOND
IS
BEGIN
 RETURN CAST ( end_date_in AS TIMESTAMP WITH TIME ZONE )
      - CAST ( start_date_in AS TIMESTAMP WITH TIME ZONE );
END date_diff;
/

For the full Q&A, click here.

Bi-directional cursor access?

Sadly, Oracle does not yet support bi-directional access to cursor result sets (a.k.a, "scrollable cursors") through a PL/SQL interface. You might well find, however, that you can achieve the desired effect with a combination of the following:

• Multiple queries (each with different ORDER BY clauses that correspond to the different ways you need to traverse the result set)
• Analytic functions: As the SQL Reference states, "Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row..."

Fortunately, you can achieve the desired effect of a bi-directional cursor rather easily by caching the result in a PL/SQL collection. Once the data has been moved into the cache, you can move back and forth through the result set, compare rows, etc., with complete freedom and a high degree of efficiency.

Hiding code from view

Oracle offers two ways to hide your code:

1. "Wrap" or obfuscate your code so that it can still be compiled and executed, but cannot be read
2. Hide program implementations within a package body

For the full Q&A, click here.