The More You Know

Session three turned out to be great! The subject matter was on error handling in PL/SQL and how while Oracle 10gR2 has fixed a few issues with PL/SQL error handling, there is much more that each developer can do to increase the usefulness of errors to developers and the verbosity for users. As a bonus it was given by Steven Feuerstein who has a great presentation style. I will admit I do not agree with everything he presents, but at least he does it in a way that keeps me listening.

What he did covers was usefulness of the DBMS_UTILITY package and how SQLERRM is a thing of the past and how should remain that way by not using SQLERRM ever again. Instead , DBMS_UTILITY.FORMAT_ERROR_STACK (which is the same as SQLERRM) should be used as well as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_CALL_STACK. The latter provides line numbers (finally!) of where in a file the error actually occurs. It is also worth noting that the output from the last two function calls can be reset by re-raising errors via “raise;”. Also to help he noted the use of DBMS_ERRORLOG() and how it can be used to log error over an entire processes life.

However, the big bang comes from how you can tie all of that available information together to provide incredible error logging and even methodologies for storing and referring to errors by names instead of numbers. Instead of covering them here, I will point you to Steven’s error handling package (which is freeware, however not supported by Oracle) at Steve’s site here. It is defiantly worth a read through and consideration of use or at least emulation.

Steve also has the following site to help push the PL/SQL curators (one of who I had the chance to meet!) at http://www.iloveplsqland.net/. Trivia fact, the site is also run on Oracle’s APEX.

The second section I attended was title “Dynamic PL/SQL.” The presentation was done by Dr. Paul Dorsey and supported by Michael Rosenblum. The topics covered were quick and rapid. However I was able to pull out a list of interesting topics covered and even some detail. The presentation was very technical and thusly was hard to write about as being done. Also the depth taken is farther than a quick blog post will do justice for.

Firstly, the quirks of dynamic SQL that were covered were the 32k length limit of a SQL query. In 8i, you could concatenate any length together and the query would execute, however it is not supported by Oracle. Also, if you are using a character set that uses one byte lengths, you can effectively fit up to 64k with a similar operation. However, this practice raises portability concerns. Another quirk is that in 10g and higher, when using dynamic SQL to drop an index it will invalidate any objects that rely on that table, mainly views. To avoid this, turning trace level 12 on before and after the statement avoids the problem completely!

The biggest topic of preference was performance. Dynamic SQL is more performance intensive than executing embedded SQL. However, the overhead only becomes and issue when the queries are executed in a loop. For most situations either aggregating the data or using bulk operations can relieve the issues of dynamic overhead. Also, while in a stateful environment, it is possible to use cursor operations with limits, to reexecute the code (almost as if a continuation if that helps) to poll for data over large sets. This however is not possible in stateless environments (e.g. web applications). Of course, bind variables were stressed as that is the universal tip expressed in all Oracle talks. Another topic breached was the use of EXECUTE IMMEDIATE over DBMS_SQL or even reference cursors of DBMS_SQL as they execute up to five times faster. (!!!) The only redeeming quality DBMS_SQL has is the ability to work with unknown input and outputs variables.

A few tricks when creating generic process for table access was the use of row types and”SELECT *” queries. This allowing reference cursors to be given a type of SYS_REFCURSOR while reducing the maintenance overhead of correlating what is selected to record types. Also was the use of database objects to be created on the fly in SQL statements using as well as FORALL loops that perform EXECUTE IMMEDIATE with USING clauses (which is the only supported variety).

Over all the presentation was quick and lots of techniques that may or may not be of use to the listening audience. For the novice or intermediate looking for more information on advanced dynamic SQL, the talk would nearly be useless as it was a blur of very technical details. However, for the dynamic SQL savant it provided some interesting details and possibly different ways at attacking common problems.

The key note speaker for OPP 2007 east was Steven Feurstein from Quest Software. Being a key note speaker his job is to welcome everyone to the conference and bring an over all tone to conference. What Steven did was bring to light many topics that are both generically interesting to all programmers, no matter the language and environment, but also bring something that the PL/SQL community is lacking, modern methodologies. Ideas like extreme programming, test driven development, and agile development.

Since my induction into the world of PL/SQL, I have noticed a near complete lack of progressive programming tactics. This can be seen in the types of tools that are present in the market that cover PL/SQL. What you will find is that the leading companies offering tools, is in fact Quest Software. This is not to say that all PL/SQL programmers are ignorant to these new topics. However, I think the presence of new methodologies has been lacking in presence. Even simple ideas of unit testing seem to escape the community. Maybe it is due to a lack of tools…

Steven is also a very quirky guy who appears to have a very solid interest in health and fitness, he even opened part of his speech on ergonomics and the need to drink more water. A quire refreshing topic as I have seen too many developer’s let their bodies waste away due to lack of exercise and the consumption of quick food. Either way you look at it, programmatically, personally, or even fitness wise Steven’s opening notes were worth the time spent and he even threw in quite a few sly remarks that came off as big laughs.