New Oracle PL/SQL Error Management Framework Released

Error management in Oracle PL/SQL should be part of your application design and should follow a standard process for handling and raising errors. The good news is that you do not have to design and code your error management system yourself because Steven Feuerstein has done all the work for you.

During the seminar that I attended a couple of weeks ago, Steven announced the early release of a new product called the Quest Error Manager or QEM for short.

So, what is QEM? From the product documentation:

QEM is a framework that will help you standardize the management of errors in Oracle PL/SQL-based applications. It consists of the q$error_manager package and several underlying tables that store information about errors that occur in an application.

Moreover, QEM offers a single package to help you trace the execution of your application. The basic features are as follows:

• Write trace information out to the q$log table or to the DBMS_OUTPUT buffer.
• Selectively turn on and off tracing through calls to the q$error_manager procedures.
• Trace execution of code by a context string, and then also include strings, numbers, dates or Boolean values.

The Quest Error Manager is a freeware product and is not supported by Quest Software. You can download it as a zip file from oracleplsqlprogramming.com/downloads/qem.zip.

Lastly, I suggest you read Steven’s PowerPoint presentation Making the Most of PL/SQL Error Management Features. If you do not have Microsoft PowerPoint, you can still read the document from within your browser on scribd.

3 Comments | Filed in Oracle | Tags: pl/sql

---

Oracle Database 11g New PL/SQL Features in your 10g Code Today

So, you are all excited about the new PL/SQL features in Oracle database 11g and you cannot wait until you upgrade your 10g database. Well, you do not have to wait. I will show you a simple way to put, and successfully compile, any new 11g PL/SQL feature inside your 10g PL/SQL code.

Let’s say you have just read about this new feature in the Oracle 11g database called “Function Result Cache”, which allows you to request that the function’s result be cached after the first call resulting in a much faster execution of this function in subsequent calls.

Function Result Cache is a great new 11g feature. But, too bad you are still on 10g. You have two options, the first is to wait until you upgrade to 11g, which, I suspect, may not be anytime soon. The second option is to prepare your 10g PL/SQL code to use this 11g feature now, while it is fresh in your mind. To be able to do this, you will have to use the combination of DBMS_DB_VERSION and conditional compilation.

Here is an example (tested in 10g XE in the HR schema):

SQL> CREATE OR REPLACE FUNCTION all_regions
  2     RETURN sys_refcursor
  3
  4      $IF DBMS_DB_VERSION.VER_LE_10_2
  5      $THEN
  6      $ELSE
  7          result_cache relies_on (regions)
  8      $END
  9  IS
 10     l_regions_cur sys_refcursor;
 11  BEGIN
 12     OPEN l_regions_cur FOR
 13        SELECT *
 14          FROM regions;
 15     RETURN l_regions_cur;
 16  END all_regions;
 17  /

Function created.

Lines four to eight mean that if the database version is less than or equal to 10.2 then do nothing. If the database version is greater than 10.2, like 11.0 for example, then instruct the compiler to include result_cache relies_on (regions) in the compilation of the program.

Let’s test calling the function:

SQL> DECLARE
  2     l_regions_cur   sys_refcursor;
  3     l_regions_rt    regions%ROWTYPE;
  4  BEGIN
  5     l_regions_cur := all_regions;
  6     LOOP
  7        FETCH l_regions_cur
  8         INTO l_regions_rt;
  9        EXIT WHEN l_regions_cur%NOTFOUND;
 10        DBMS_OUTPUT.put_line (l_regions_rt.region_name);
 11     END LOOP;
 12     CLOSE l_regions_cur;
 13  END;
 14  /
Europe
Americas
Asia
Middle East and Africa

PL/SQL procedure successfully completed.

So, when you upgrade to 11g, all what you have to do is to re-compile your stored function and, boom!, the 11g new feature will be activated. Nice! Of course, in real world, you will have stored packages instead of standalone functions and procedures.

Note that I have not tested the above code in 11g. If you are an 11g beta tester, I would appreciate it if you let me know if the above works as expected.

Here are a few new PL/SQL features in 11g that Jurgen Kemmelings from AMIS has blogged about and that you may want to “conditionally compile” in your 10g database:

• The CONTINUE statement
• The use of sequence.nextval
• The FOLLOWS Clause
• The function result cache

And here are a couple of white papers from Oracle (PDF):

• PL/SQL conditional compilation
• Oracle Database 11g Application Development

5 Comments | Filed in Oracle | Tags: pl/sql

---