Since dbms_utility.format_error_backtrace has been available from Oracle 10g this isn’t another what it ishow to use it post. Instead I will be taking this opportunity to discuss a nuance of dbms_utility.format_error_backtrace that you may not be aware of.

dbms_utility.format_error_backtrace reports the call stack back to the exception or the last raise statement which if you are not aware of could catch you out.

A quick word on the examples that follow, I am not proposing you should use this functionality in its raw format as I have done here. I believe that for production code calls to dbms_utility.format_error_backtrace should be encapsulated within your exception handling package.

The following code creates 3 procedures. procedure a calls b and b calls c. Within procedure c an exception is raised. There is no intermediate exception handling and the exception passes back to procedure a which makes a call to dbms_utility.format_errror_backtrace.

create or replace procedure a
is
begin
 dbms_output.put_line('In procedure a');
 b();
exception
 when no_data_found then
 dbms_output.put_line(dbms_utility.format_error_backtrace);
end a;
/

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
end b;
/

create or replace procedure c
is
begin
 dbms_output.put_line('In procedure c');
 RAISE NO_DATA_FOUND;
end c;
/

When procedure a is run, the following results are observed:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.C", line 5
ORA-06512: at "HR.B", line 5
ORA-06512: at "HR.A", line 5

The error stack shows the exception started on line 5 (line 24) . Excellent, when things go wrong we can track the problem down to the line number! However procedure b needs to change and the developer, Gary Careless makes the required enhancements, one of the changes is the introduction of a new exception handling block that includes a RAISE statement.

The amended version of procedure b is now:

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
 -- lots of new functionality here
exception
 -- new exception handler for the new functionality
 when others then
 -- do something here and re-raise the exception
 RAISE;
end b;
/

Now when procedure a is run again we see the following:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.B", line 11
ORA-06512: at "HR.A", line 5

Oh no! we have lost the line number where the exception occurred. Instead of displaying that the exception was raised by line 5 of procedure c as it did in the first example, the callstack reports the exception location is now at the the location of the RAISE statement within procedure b!

To get the accurate output from dbms_utility.format_error_backtrace you need to call it

• In the exception section that first raised the exception

or

• Call it from the outermost PL/SQL block and ensure that the intermediate routines do not raisere raise the exception.

The location of exception handling within an application is a project wide decision which should be agreed before development begins. However if your error handling package includes a call to dbms_utility.format_error_backtrace then you should keep this nuance in mind.

The inspiration for this article came from reading Mark Hoxey excellent post on avoiding ORA-04068: existing state of packages has been discarded One of the potential solutions to this problem suggested by Mark is to use Application Context. I didn’t know too much about Application Context so this post is my way of documenting and sharing what I learnt.

What is Application Context?

Application Context a set of name value pairs stored in memory. Each context is essentially a namespace which contains one or more name value pairs. Application Context is a large subject and Oracle list a number of potential uses cases for Application Context.

My goal for this post is not to rewrite the documentation but to provide a walk through of creating an Application Context that could serve as replacement for the constant or variable data that is often duplicated in various package bodies scattered throughout your application.

All the examples in this article were built using Oracle’s pre-built Database App Development Virtual Machine which used Database version 11.2.0.2.

Example

CREATE OR REPLACE CONTEXT user_ctx USING pkg_user_application_context
/

The first line creates an Application Context with the name user_ctx. The USING clause identifies the PL/SQL package that can set or reset the context variables. In this example pkg_user_application_context is used. It is worth mentioning that the package specified by the USING clause does not need to exist at the time when the context is created but it must exist at run time. So the next step is create pkg_user_application_context

CREATE OR REPLACE PACKAGE pkg_user_application_context
IS

 PROCEDURE set_context_values;

END pkg_user_application_context;
/

CREATE OR REPLACE PACKAGE BODY pkg_user_application_context
IS
 PROCEDURE set_context_values
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

 -- Pretend look ups from HR tables happens here...

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'hire_date', TO_CHAR(ld_hire_date, 'YYYYMMDD'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'department_id', TO_CHAR(ln_department,'99999'));

 DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', lv_is_a_manager);

END pkg_user_application_context;
/

The package body shows a look up of various values from tables (which are not shown to keep the example from becoming too bloated) These values are then used by the call to DBMS_SESSION.SET_CONTEXT to create a number of name value pairs under the context created earlier.

A couple of points worth making; the values are stored as characters so for numbers and dates the appropriate calls to TO_CHAR are required and all the values created belong to the same Application Context, user_ctx

If an attempt is made to change the values of the Application context outside of the package pkg_user_application_context such as the following anonymous block :

BEGIN
   DBMS_SESSION.SET_CONTEXT('user_ctx', 'is_a_manager', 'Y');
END;
/

you will see the  following error message:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at line 2   

With both the Application Context and supporting package created we can now go ahead and start using the values within our application:

CREATE OR REPLACE PACKAGE BODY pkg_some_other_package
IS
 PROCEDURE interesting_hr_stuff
 IS

    ld_hire_date      DATE;
    ln_department     NUMBER(5);
    lv_is_a_manager   VARCHAR2(1);

 BEGIN

    ld_hire_date := TO_DATE(SYS_CONTEXT('user_ctx','hire_date'), 'YYYYMMDD');

    ln_department := TO_NUMBER(SYS_CONTEXT('user_ctx','department_id'));

    lv_is_a_manager := SYS_CONTEXT('user_ctx','is_a_manager');

    -- now work with the variables 

 END interesting_hr_stuff

END pkg_some_other_package;
/

The example shows another package (the spec is not shown) where the values are retrieved from the Application Context using the built in SYS_CONTEXT. The values are explicitly converted back to the expected data types and then used within the application.

Summary

In this article I have explained what an Application Context is and demonstrated how to create one use as a replacement for storing information that was previously held in package body constants and variables.

Acknowledgements

Mark Hoxey

In versions of the Oracle Database prior to 11g Release 1, to populate a variable with the next value from a sequence you would have call the NEXTVAL function from within a SQL statement. From Oracle Database 11g Release 1 you can now use the NEXTVAL function in a PL/SQL expression.

So other than less typing, does using a sequence in this way improve performance?

The following examples are all based on the following sequence

CREATE SEQUENCE emp_seq
/

and were developed using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10.

To recap, the pre 11g method was:

...
SELECT emp_seq.nextval
  INTO some_variable
  FROM dual;
...

and from 11g Release one you can use the following:

...
some_variable := emp_seq.nextval;
...

Better Performance?

Using the method described by Adrian Billington you will be able see if the 11g method improves performance.

ALTER SESSION SET SQL_TRACE = TRUE
/

DECLARE

   emp_id PLS_INTEGER;

BEGIN

   FOR i IN 1 .. 10000
   LOOP

      emp_id := emp_seq.NEXTVAL;   

   END LOOP;

   FOR i IN 1 .. 10000
   LOOP

      SELECT emp_seq.NEXTVAL AS nval
        INTO emp_id
        FROM dual;

   END LOOP;

END;
/

SQL Trace is enabled and the new 11g method of using NEXTVAL via an expression is called 10000 times after which the pre 11g method of using a SQL statement is also called 10000 times. Note the alias of nval is used with the SQL statement method this will help us identify the SQL statement method.

Here is the output from the trace file. The first entry is for the new 11g method and the second entry is for pre 11g method (as proved by the use of the alias nval)

The trace file shows very little differences when using the new 11g method. Which is not surprising as the trace file also shows that “under the covers” Oracle has not changed the implementation for the new 11g method and NEXTVAL is still obtained via a SQL statement.

Summary

In this article I have shown the two methods that are available to populate a variable with the next value from a sequence and that there is no performance gains to be had by choosing one method over another.

Acknowledgements

Adrian Billington (oracle-developer.net)