OPP/News - April 2006

Dear Oracle technologists,

Not so ODDLY IN

In the March 2006 newsletter, I wrote the following:

I recently received an email from a puzzled developer. So...what's wrong with the following scenario?

SQL> SELECT 'SQL IN trimmed trailing blanks!'
 2 FROM DUAL
 3 WHERE 'CA ' IN ( 'CA', 'US' )
 4 /

'TRIMMEDTRAILINGBLANKS!'
------------------------
SQL IN trimmed trailing blanks!

Isn't that odd? I asked if the string "CA " (CA followed by three spaces) was in the list of 'CA' and 'US' -- and Oracle said yes! I checked the documentation but could not find anything about automatically trimming blanks when you use the IN clause.

Well....I might think this behavior is odd, but several readers (including Paul Majewski, Chip Dawes and Steve Tolkin) wrote in to say, most politely but in effect, "RTFM!"

In fact, Oracle does document this behavior, as Chip showed with an extract from the SQL Reference:

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

Sorry about increasing the confusion level, instead of clearing things up!

PL/SQL and Sudoku

A very clever fellow (and fine PL/SQL programmer) named, Phillip Lambert wrote the following to me recently:

Hello Steven,

I'm not sure how much Sudoku puzzles have caught on in the US, but they are fairly big in the UK, particularly in all of the daily papers. Well I've probably been using PL/SQL much too long or probably am well due for retirement out of computers, but I was presented with a challenge the other day which some might consider quite sad. I wrote a Sudoku puzzle solver in SQL and PL/SQL which solves most puzzles presented to it.

The proud thing about it is that a colleague of mine who gave me the idea wasn't able to get his MS Excel VBA solution to work properly. Another colleague also took up the challenge, and again could not get his Java solution to fully work and has spent weeks compared to my days programming it. I'm not sure whether this is saying something for SQL and PL/SQL, but in any event I thought you might be interested. This could be a great opportunity to see whether anyone can use an alternative language to write a solution in less lines of code than a PL/SQL solution - now that would be a challenge!!

To check out Phillip's implementation of a Sudoku puzzler, click here.

My friend, Patrick Barel, found a Transact-SQL implementation at here. He has also built a Delphi interface to Phillip's Sudoku effort, so you can easily run the backend code. By the way, Patrick quickly built this by using QCGU (www.qcgu.net) to generate a table API to Phillip's tables.

In addition, Doug Case wrote in to say that he had also produced an Oracle implementation of Sudoku, using SQL and Application Express (formerly HTML DB). Check it out at here.

PL/SQL and PDF documents

I am, as most who read my books and my blog must know, quite obsessed with the Oracle PL/SQL langauge. So I am always on the lookout for new and amazing things people are doing with PL/SQL. I recently came across...

PL/PDF - www.plpdf.com

"Generate dynamic PDF documents from data stored in Oracle databases using the PL/PDF program package. PL/PDF is written exclusively in PL/SQL. It is able to either store the generated PDF document in the database or provide the results directly to a browser using MOD_PLSQL. No third-party software is needed; PL/PDF only uses tools provided by the installation package of an Oracle Database (PL/SQL, MOD_PLSQL). Use PL/PDF to quickly and easily develop applications with dynamic content but also quality presentation and printing capabilities."

Sounds like fun! I have only played around with PL/PDF a little bit, but it looks like they have put together a very nice, clean API to the underlying functionality, making it extremely easy to use PL/PDF. To give you a sense of that, check out the explanation of their approach.

You can trial PL/PDF by downloading the software and specifying 'TRIAL' word as the certification key. Limitations: max. 5 pages, watermarked pages.

My compliments and best wishes to Laszlo Lokodi and others at PL/PDF for showing how useful and flexible PL/SQL can be!

Useful code: String Tracker

I have been very busy lately building Qute, the Quick Unit Test Engine (www.unit-test.com). One of things this tool does is generate test code (a package) from your test definition. This test package declares local variables for each unit test and test case. I need to make sure that the same variable is not declared more than once. So whenever I generate a declaration, I want to remember that this variable name has already been declared or "used." Then I can check the next variable to be declared against this list and see if it is already taken care of, or not.

In other words, this is the kind of code I wanted to write:

string_tracker.clear_list ( 'declared_variables' );

l_row := test_elements_in.FIRST;

WHILE ( l_row IS NOT NULL )
LOOP
   -- Not yet declared if not on the list.
   IF NOT string_tracker.string_in_use (
             'declared_variables'
           , test_elements_in ( l_row ).name)
   THEN
      gen_declaration (test_elements_in ( l_row ));
      
      string_tracker.mark_as_used ( 
         'declared_variables'
       , test_elements_in ( l_row ).name
      );
   END IF;

   l_row := test_elements_in.NEXT ( l_row );
END LOOP;

Here, then, is the specification of my string_tracker package:

CREATE OR REPLACE PACKAGE string_tracker
/*
Overview: String_tracker allows you to keep track of whether a
certain name has already been used within a particular list.

Author: Steven Feuerstein

You are permitted to use this code in your own applications.

Requirements:
   * Oracle9i Database Release 2 and above

*/
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2 ( 32767 );

   PROCEDURE clear_all_lists;

   PROCEDURE clear_list ( list_in IN maxvarchar2_t );

   -- Is the string already in use?
   FUNCTION string_in_use (
      list_in             IN   maxvarchar2_t
    , value_in            IN   maxvarchar2_t
    , case_sensitive_in   IN   BOOLEAN DEFAULT FALSE
   )
      RETURN BOOLEAN;

   -- Mark this string as being used.
   PROCEDURE mark_as_used (
      list_in             IN   maxvarchar2_t
    , value_in            IN   maxvarchar2_t
    , case_sensitive_in   IN   BOOLEAN DEFAULT FALSE
   );
END string_tracker;
/

Perhaps you will also find this package useful; you can download it by clicking here. The implementation is very simple, because it takes advantage of multi-level, string-indexed associative arrays. If you are not yet familiar or comfortable with collections in PL/SQL or with these latest features of collections, study string_tracker!

Upcoming seminars and public appearances

Apr 12, 2006—Brooklyn Park, MN
21st Century SQL Training
Sponsored by the Twin Cities Oracle Users Group

April 24, 2006—Nashville, TN
COLLABORATE 06, the Technology and Applications Forum for the Oracle Community
I will be presenting "529: Six Steps to Unit Testing Happiness" at Collab'06, the latest incarnation of IOUW (International Oracle User Week), the annual conference organized by IOUG.
More information here.

May 31st to June 2nd, 2006—Edina, MN
High Performance, Optimized Code through Collections
Sponsored by the Twin Cities Oracle Users Group

Jun, 2006—Washington, DC
ODTUG Kaleidoscope 2006 I will be presenting twice at this must-attend Oracle conference.

Aug 18, 2006—San Ramon, CA (San Francisco/East Bay)
21st Century SQL Training
Sponsored by the Northern California Oracle Users Group

Sep 12, 2006—Victoria, British Columbia
21st Century PL/SQL Seminar
Sponsored by the Victoria Oracle Users Group