March 2006 OPP/News

OPP/News - March 2006

Dear Oracle technologists,

I hope your winter (if you experience the season) is as pleasant as ours in Chicago has been....we've had only one substantial snow (of which I took immediate "advantage" by destroying the splash guards on the bottom on my very low-riding Honda Insight. Sigh...) and the temperatures have been so moderate that I have only worn my winter coat 2 or 3 times! Ah, global warming has been good to Chicago....Ha! And now as I write this, the air outside my window is filled with wonderful, swirling snow!

I continue to keep very busy developing Qute, the Quick Unit Test Engine. We have gotten rave reviews from early users/testers (it is not yet in production) and are now working hard to make it qualitatively more flexible and accessible. I encourage you to check it out. I feel confident in saying that there is no better way to organize, develop and run unit tests for your PL/SQL programs.

So...in this month's newsletter, you will read about:

  • Some odd behavior with the IN clause in SQL, and yet another reason to use PL/SQL all the time, whenever possible, obsessively, compulsively.
  • Glitches I encountered with Oracle Express Edition.
  • A new, minor, but very useful feature in Toad 8.6.

Oddly IN

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. So I experimented some more:

1. Trims tabs, too:

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

'SQLINTRIMMEDTRAILINGTAB!'
----------------------------
SQL IN trimmed trailing tab!

2. Does not trim leading spaces:

SQL> SELECT 'SQL IN does not trim leading blanks!'
 2 FROM DUAL
 3 WHERE ' CA' IN ( 'CA', 'US' )
 4 /

no rows selected

3. And most important of all: does not trim anything at all when you execute the same query inside a PL/SQL block or simply use the native PL/SQL IN clause:

DECLARE
   PROCEDURE exec_in ( message_in IN VARCHAR2, value_in IN VARCHAR2 )
   IS
      l_dummy VARCHAR2 ( 32767 );
   BEGIN
      BEGIN
         SELECT message_in
         INTO l_dummy
         FROM DUAL
          WHERE value_in IN ( 'CA', 'US' );
          
         DBMS_OUTPUT.put_line ( message_in );
      EXCEPTION
          WHEN OTHERS
          THEN
          DBMS_OUTPUT.put_line ( message_in || ' did not happen.' );
      END;
       
      IF value_in IN ( 'CA', 'US' )
      THEN
         DBMS_OUTPUT.put_line ( '"PL/SQL IN ' || SUBSTR ( message_in, 9 ));
      ELSE
          DBMS_OUTPUT.put_line ( '"PL/SQL IN '
                || SUBSTR ( message_in, 9 )
                || ' did not happen.'
                );
      END IF;
   END;
BEGIN
    exec_in ( '"SQL IN trimmed trailing blanks."', 'CA ' );
    exec_in ( '"SQL IN trimmed leading blanks."', ' CA' );
    exec_in ( '"SQL IN trimmed trailing tab."', 'CA ' );
END;
/

"SQL IN trimmed trailing blanks." did not happen.
"PL/SQL IN trimmed trailing blanks." did not happen.
"SQL IN trimmed leading blanks." did not happen.
"PL/SQL IN trimmed leading blanks." did not happen.
"SQL IN trimmed trailing tab." did not happen.
"PL/SQL IN trimmed trailing tab." did not happen.

The lesson we learn once again: write everything in PL/SQL!

Ah, just kidding. You should continue to choose the best tool for the challenge at hand. But in this case, avoid funkiness with IN by executing your query within a PL/SQL block!

Glitches with Oracle Express Edition

Oracle recently announced production/general availability of Oracle Express Edition (XE). Generally a very exciting development: a totally free, almost fully-functioned version of Oracle Database 10g that downloads and installs in minutes (well, assuming you've got broadband!). I ran into a couple of glitches, however, that I thought I would share:

1. UTL_FILE is not available. Usually, when you install Oracle, the UTL_FILE package (used to read/write files within PL/SQL) is installed, EXECUTE is granted to PUBLIC, and a public synonym is created. With XE, the package is installed and the synonym created, but the GRANT EXECUTE has not been run. To fix this problem, connect to a SYSDBA account and run the $ORACLE_HOME/RDBMS/Admin/utlfile.sql file, or simply execute this command (from a SYSDBA account):

GRANT EXECUTE on SYS.UTL_FILE TO PUBLIC
/

2. Oracle XE does not include a Java Runtime Environment. I have been using some Java classes, installed in the database, in my unit testing product, Qute. Here is an example of a call to this Java code: 

PROCEDURE parse_package (
   owner IN VARCHAR2 
 , package_name IN VARCHAR2 
 , program_name IN VARCHAR2 ) 
AS LANGUAGE JAVA name 'quPlSqlHdrParser.parsePackage(
                          java.lang.String, java.lang.String, java.lang.String)';

In pre-production versions of Oracle XE, the code compiled, but then raised runtime errors, sometimes ORA-00600, which are not trap-able with an exception section.

The production version still does not include a JRE and even worse, if you install the Oracle Database 10g Express Edition (Western European; " Oracle Database 10g Express (Western European) Edition - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only.") then the above code will not even compile. Very strange. Oracle seems to actually be looking at the literal string and attempting to validate it at compile time. Yet if I install the Oracle Database 10g Express Edition (Universal; Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish), then I do not get a compilation error.

Improved Program Navigator in Toad 8.6

Disclosure: I am an employee of Quest Software. My title is delightful: Senior Technology Advisor. This means, among other things, that I get to use Toad, SQL Navigator, and the many other excellent Quest tools at no cost! Such a lucky guy....

Toad is a great tool, as its 500,000+ users will attest, but there is always room for improvement. Toad offers a Program Navigator to help you find subprograms and other elements within your Procedure Editor. It is fantastically helpful, especially when you make extensive use of local or nested subprograms, as I do.

One drawback in the past, however, has been that the programs were always and only listed in the Navigator in the order they were defined in the package or declaration section of the subprogram. Here is an example of that Program Navigator ordering:

http://www.oracleplsqlprogramming.com/newimg/toad_prognav1.jpg

Notice that the program names are not alphabetical. This is a fine presentation; it is good to see the organization of my code. Sometimes, however, it is hard for me to find the program (like when my package has 50 subprograms!). I know the name, but where does it appear in the package?

Toad 8.6 now gives you the ability to order the program names alphabetically, by clicking on the little green and red icon, which is right next to the refresh icon:

http://www.oracleplsqlprogramming.com/newimg/toad_prognav2.jpg

This is a very handy feature and I use it often. The Toad team has also improved the organization of information in this Navigator, and will be doing even more in the future with this feature.