This note inaugurates OPP/News, a monthly e-newsletter focusing on the topic of Oracle PL/SQL programming. Each newsletter features a Tip of the Month, Useful Code of the Month, and more. Before we get to all that, however, I would first like to announce three new PL/SQL projects in which I am involved:

1. Oracle PL/SQL Programming 2005 - a two-day PL/SQL conference to be held on November 2-3, 2005 in Chicago. Featuring in-depth presentations by Steven Feuerstein (don't you love it when people mention themselves in the third person?), Bryn Llewellyn (PL/SQL Product Manager) and Marchel Kratochvil (Winner of the 2004 Oracle Magazine PL/SQL Developer of the Year award). OPP2005 is a prime learning opportunity for PL/SQL developers. It will also be a birthday party: we will be celebrating the 10th anniversary of the publication of Oracle PL/SQL Programming! Space is limited, so I encourage you to register early.

2. 21st Century PL/SQL Idea Collector - PL/SQL is a mature, robust language, but that doesn't we don't each have our own list of things we'd like to see added or changed in PL/SQL to make it even better. Now you have a way to share your ideas with other developers and with the PL/SQL development team. The 21st Century PL/SQL Idea Collector allows you to submit suggestions and rank the ideas of others. These recommendations will then be presented to Bryn Llewellyn at the Oracle PL/SQL Programming 2005 conference.

3. Oracle PL/SQL Spotlight Series - The Spotlight Series is a joint collaboration between myself and Pinnacle Publishing, publisher of the Oracle Professional newsletter. Spotlights are intensive, two-hour, for-fee, live trainings I will conduct on-line in June and early July 2005. This will give everyone the opportunity to "attend" my trainings from the comfort of their own work environments, and in smaller chunks of time. I believe this will be a great way to deepen your knowledge of the PL/SQL language, and I hope that you will agree! The regular cost for a Spotlight webinar is $99, but subscribers to OPP/News can take advantage of a special discounted price of $59 per webinar by clicking here.

And now on to the OPP/News regular features....

Tip of the Month

Did you know that SQLERRM, which returns the message associated with the current error, is restricted to either 255 or 512 characters, depending on your version of Oracle? Because of this, Oracle recommends that you instead call DBMS_UTILITY.FORMAT_ERROR_STACK, which will return the full error message without truncation! Of course, you should watch out for trying to display the value returned by this function using DBMS_OUTPUT.PUT_LINE, since it will throw an exception if the string is more than 255 characters long.

Useful Code of the Month

Wouldn't it be absolutely splendid if the Oracle database supported a Boolean datatype for table columns? Since it does not, we have to define columns as "pseudo-Booleans," such as a VARCHAR2(1) column containing Y or N. But then our code needs to know how to translate those values into true PL/SQL Booleans -- and vice versa. So what we need is a Boolean Translation Package to make sure we can do that consistently. This download, by the way, comes from the Best Practice PL/SQL column that I author on the Oracle Technology Network.

Recent PL/SQL Writings of Note

Actually the following writings have been available on OTN for a while now, but few people know of them, so I thought I would bring them to your attention. Oracle Database 10g Release 10.1 introduced an optimizing compiler that can double the performance of PL/SQL code, which is quite wonderful. Many developers naturally have questions about how the optimizer works, and Oracle has posted on the OTN PL/SQL page a number of very interesting and thorough whitepapers:

PL/SQL Just Got Faster explains the workings of the PL/SQL compiler and runtime system and shows how major improvements on this scale are indeed possible.

PL/SQL Performance Measurement Harness describes a performance experiment whose conclusion is the large factors quoted above. Oracle provides a downloadable kit to enable you to repeat the experiment yourself.

Freedom, Order, and PL/SQL Optimization, intended for professional PL/SQL programmers, explores the use and behavior of the new compiler.

PL/SQL Performance — Debunking the Myths, again intended for readers who work at the PL/SQL codeface, re-examines some old notions about PL/SQL performance.

Upcoming public trainings

Toad User Group seminars throughout the United States and in Toronto in May 2005: Quest is offering a series of half-day seminars for Toad users. I will be presenting on the topic of "You did what? Common mistakes in Oracle PL/SQL Programming."

UNYOUG Seminar on June 10 in Rochester, NY: A day-long presentation of my 21st Century PL/SQL seminar.

ODTUG on June 20 - New Orleans: Monday at 1:30 PM: Programming Humility: Dealing with the Reality of Errors; Tuesday at 9:30 - Too Much Code, Too Little Time: How Qnxo Can Help.

That's probably enough for one newsletter. I hope that you have enjoyed OPP/News and can put this information to use. If you have any feedback on this newsletter, don't hesitate to get in touch. And do pass it along to anyone you think would benefit.