The More You Know

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.