Is there more than one reason to use PL/SQL WHERE CURRENT OF ?

I can’t remember using the PL/SQL construct where current of  in code I have written so along with my usual article style of how use to use this feature, this is also an experiment to find out if there are any other reasons to use where current of.

The code examples in this post have been developed using the Oracle Database App Development VM for Oracle VirtualBox which at the time of this post was using Oracle database 12.1.0.2. (Although nothing in this post is 12c specific)

First I create a table that can be used for the experiment


create table t
as
select *
from all_objects
/

With the table created, I then use it in the following example which I will explain below.  At this point I would like to say that I appreciate the example could be rewritten as a single SQL statement however I wanted to concentrate on illustrating the use of where current of  without an overtly complicated example where the point of this post is lost in a forest of code.


declare

  cursor c 
  is 
  select t.object_name
    from t
   where t.object_type in ('PACKAGE', 'PACKAGE BODY')
     and t.owner = 'APEX_040200'
     for update of object_name; 

  ln_count pls_integer := 1;

begin

  for i in c
  loop
    
    update t
       set t.object_name = 'X - ' || ln_count
     where current of c;
 
    ln_count := ln_count + 1;
 
  end loop;

  commit;  

end;

At line 3 I declare a cursor that identifies the records of interest, which are the package specifications and bodies owned by the APEX user.  Note the use of the for update clause. This is mandatory when using where current of . If you remove it and try to run the example you will see the following error message

PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF

Moving on to the execution section,  for each iteration of the cursor for loop,  the object name is updated (to something not very useful!) and you can see at line 20 what I think is the primary benefit of using where current of is that instead of repeating the where clause used by the cursor I have used where current of c. So when the cursor has to change to return different records the only change that needs to be made is to the cursor.

The final part of the example is once the loop completes I release the lock on the table by committing the changes.

The primary benefit of where current of is the reduction in duplicated code which is a good thing but am I missing anything else? Please use the comments to let me know.

Summary

In this post I have demonstrated how to use the PL/SQL construct where current of along with what I believe is it’s primary benefit, the reduction of duplicated code.

Acknowledgements

The inspiration for this post came from Steven Feuerstein’s book, Oracle PL/SQL Programming  5th Edition.

 

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement

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.

PL/SQL FizzBuzz

One of the popular exercises when learning a new language is to develop a FizzBuzz program.

This is where you print out the all the numbers from 1 to 100 except if a number is a multiple of 3 you print out Fizz and if the number is a multiple of 5 you print out Buzz. If a number is a multiple of both 3 and 5 then you print out FizzBuzz.

I came across this challenge for the first time recently as I learn C# so here is my version of FizzBuzz written in PL/SQL.


DECLARE

   fizz BOOLEAN := FALSE;
   buzz BOOLEAN := FALSE;

BEGIN

   FOR i IN 1 .. 100
   LOOP

      fizz := MOD(i, 3) = 0;
      buzz := MOD(i, 5) = 0;

      CASE

         WHEN fizz AND buzz THEN

            dbms_output.put_line('FizzBuzz');

         WHEN fizz THEN

            dbms_output.put_line('Fizz');

         WHEN buzz THEN

            dbms_output.put_line('Buzz');

         ELSE

            dbms_output.put_line(i);

      END CASE;

   END LOOP;
 
END;

 

Found a problem with the Oracle Documentation? Report it!

I was recently working through Chapter 9 of the 12c Database 2 Day Developer’s Guide and found the following bug in the sample code.

Here is the original code:

FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE
  IS
    l_department_id departments.department_id%TYPE;
  BEGIN
    INSERT INTO departments ( department_id, department_name, manager_id )
    VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id );

    RETURN l_department_id;
  END add_department;

The function creates a new department and should return the newly created department id. Except it doesn’t. The variable used to return the newly created department id, l_department_id is never set so it will always returns null.

One solution to this bug is to use the Returning Into clause and the revised code would be:

...
    INSERT INTO departments ( department_id, department_name, manager_id )
    VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
    RETURNING department_id INTO l_department_id;
 
    RETURN l_department_id;
...

It’s a trivial issue so why I am writing about it? The reason is that seeing this problem I could have easily moved on and finished the chapter but I noticed the Feedback button and thought that others will also encounter this problem and may waste time wondering why the department id is not being returned.

OFeedback

After clicking the feedback button and using my OTN account (you also have the choice of remaining anonymous), describing the issue along with the suggested fix as described above I pressed submit and thought no more about it.

Within 24 hours I had an email reply from someone at Oracle (and someone not using a do_not_reply email address) thanking me for pointing out the error and assuring me it will be fixed in future editions. Whilst it remains to be seen if this will be done, the response to my feedback has left a very positive impression with me which made me glad I made the effort to report this issue.

So if you spot something within the masses of Oracle documentation, report it and help fix those broken windows.

The PL/SQL Continue statement

Introduced in Oracle Database 11.1 The PL/SQL CONTINUE statement allows you to skip the current  loop iteration. It can be used conditionally and unconditionally.

Unconditional Example

BEGIN

   FOR i IN 1 .. 10
   LOOP
 
      IF i = 2
      THEN
         CONTINUE; 
      END IF;
 
      DBMS_OUTPUT.PUT(i || ', ');
 
    END LOOP;

    DBMS_OUTPUT.NEW_LINE;

END;

In the example above, there is no output when the loop executes iteration 2, Running this code shows the following output.

1, 3, 4, 5, 6, 7, 8, 9, 10,

 Conditional Example

BEGIN

   FOR i IN 1 .. 10
   LOOP
 
      CONTINUE WHEN i = 2; 
 
      DBMS_OUTPUT.PUT(i || ', ');
 
   END LOOP;
 
   DBMS_OUTPUT.NEW_LINE;

END;

Again in this example there is no output for iteration 2. Running this code shows the following output.

1, 3, 4, 5, 6, 7, 8, 9, 10,

The conditional use removes the need for the IF statement used in the unconditional example and makes the code more concise without losing readability.

Summary

In this article I have shown with short code examples how to use the PL/SQL Continue statement.

This article has not discussed whether the use of a CONTINUE statement is a bad programming practise, there are already enough arguments discussions about that.

Source: The Continue Statement within the Database documentation 

No surprises with Oracle 12c Identity Column performance

Oracle 12c introduced the Identity Column. You can find out more about this feature in the new features guide and within the CREATE TABLE documentation.

In this article I will use Tom Kyte’s run stats utility to compare the performance of the IDENTITY column with the explicit use of a sequence object. The script below will be used to insert 10,0000, 100,000 and finally a million rows.

The tests were performed using Virtual Box running the pre built Database App Development VM. The database version at this time was 12.1.0.2.0 and all the examples were developed using SQL Developer 4.0.3.16

To support the tests,  the following objects are required. Firstly, a table using the IDENTITY column is created. Note the syntax for creating an IDENTITY column. In addition as I know there will be some large inserts into this table I have adjusted cache size of the sequence accordingly.


CREATE TABLE t_identity(id      NUMBER         GENERATED AS IDENTITY CACHE 1000
                                               CONSTRAINT t_identity_pk PRIMARY KEY,
                        details VARCHAR2(32))
/

Next a table and a sequence which will be used to hold the results of the inserts via a regular Oracle sequence is created. Again the sequence cache size has been increased from the default.

CREATE TABLE t_seq(id      NUMBER CONSTRAINT t_seq_pk PRIMARY KEY,
                   details VARCHAR2(32))
/

CREATE SEQUENCE s1 CACHE 1000
/

Below is the test script. As a brief overview, it initialises the call to the runstats package, it then inserts the required number of records into the table with the IDENTITY column.

The runstats package is called again to show that the first part of the processing has finished and the second part is about to start. The second insert is identical to the first one with the exception of the explicit call to the sequence object.

Thanks to Oracle Base for the tip about using TIMESTAMP as a seed to dbms_random. I am not advocating using row by row processing to insert volumes of data of this size in the real world!


BEGIN

   runstats_pkg.rs_start();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_identity(details)
      VALUES(l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_middle();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_seq(id,
                        details)
      VALUES(s1.nextval,
             l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_stop(1000);

END;
/

Here is the runstats output for each of the runs

10,000 rows inserted


Run1 ran in 106 cpu hsecs
Run2 ran in 105 cpu hsecs
run 1 ran in 100.95% of the time
...
Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff  Pct
171,097   171,432   335   99.80%

100,000 rows inserted


Run1 ran in 1216 cpu hsecs
Run2 ran in 1156 cpu hsecs
run 1 ran in 105.19% of the time
...
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff      Pct
1,719,582   2,061,024   341,442   83.43%

1,000,000 rows inserted

Run1 ran in 12308 cpu hsecs
Run2 ran in 11835 cpu hsecs
run 1 ran in 104% of the time
...
Run1 latches total versus runs -- difference and pct
Run1         Run2         Diff      Pct
18,480,661   18,761,711   281,050   98.50%

and the difference between the two methods is negligible.

Summary

In this article, using Tom Kyte’s runstats utility, I have invested the performance of the Oracle 12c new feature; IDENTITY column

Acknowledgements

Tom Kyte for his seminal package runstats

 

The most important reason why you should know how to use PL/SQL Conditional Compilation.

….is that when you really need to, it allows you to turn up your applications instrumentation  to 11.

A 30 second PL/SQL Conditional Compilation overview

PL/SQL Conditional compilation was introduced with Oracle Database 10gR2. The official documentation  explains how to use the Conditional Compilation constructs but doesn’t give too many use cases. Fortunately the Oracle White Paper PL/SQL conditional compilation not only covers how to use Conditional Compilation, it also gives a number of use cases and finishes with an in depth case study.

Conditional Compilation allows you to mark your portions of your code so that a preprocessor can determine what will actually be sent to the compiler.  Rather than bore you with more words lets look at a simple example.

CREATE PROCEDURE cc_intro
IS
BEGIN

   $IF sys.dbms_db_version.ver_le_12_1
   $THEN

      dbms_output.put_line('Can use the 12c new features'); 

   $ELSE 

      dbms_output.put_line('Have to use the work arounds'); 

   $END

END cc_intro;

In this example, if the procedure is compiled on a database running 12.1 the first message will appear in the compiled version of the code otherwise the second message will appear.

On line 5 the Conditional Compilation selection directive begins with $IF. The value tested must resolve to a static boolean value such as literals, inquiry directives or as in this example a package constant. Notice that the selection directive finishes with $END and not END IF. Also there is no semi-colon after $END.

Once the procedure has been compiled, you can view the code as the compiler will “see” it using the supplied dbms_preprocessor.print_post_processed_source


BEGIN

   dbms_preprocessor.print_post_processed_source
   (
      object_type => 'PROCEDURE',
      schema_name => USER,
      object_name => 'CC_INTRO'
   );

END;
/

After running on my Oracle 12.1 database the following is seen.

PROCEDURE cc_intro
IS
BEGIN 

 dbms_output.put_line('Can use the 12c new features'); 

END cc_intro;

As expected, only the first message exists in the compiled code.

The most important reason

Following months (weeks, hours or minutes!!) of the various stages of testing,  new changes have finally made it into production. At first everything is great and is ticking along nicely. Until it doesn’t. Strange things start to happen. That thing that can’t happen is happening. To add to the complexity try as you might the problem appears to happen only in production.

You trace the problem back to a routine where perhaps a XML document is created or a collection is populated. Unfortunately you are unable to see what these normally opaque data structures contains and that information could be key to solving the issue.

A method that I have often seen to get round this problem is to wrap code with a standard IF statement as shown in Procedure B below.


CREATE PACKAGE pkg_no_cc
IS

   PROCEDURE a;

END pkg_no_cc;
/

CREATE OR REPLACE PACKAGE BODY pkg_no_cc
IS

   g_debugging BOOLEAN := FALSE;

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml        XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here...
      dbms_output.put_line('This is procedure b');

      IF g_debugging = TRUE
      THEN 

         -- see what is in the collection
         FOR i IN 1 .. l_collection.COUNT()
         LOOP

            dbms_output.put_line('see the output of the collection'); 

         END LOOP; 

         -- view the xml
         dbms_output.put_line('the xml: ' || l_xml.getstringval() );

      END IF; 

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_no_cc;
/

If the debugging statement within Procedure B survived a Code Review and made it
into production it would cause an overhead, no matter how small (they all add up) every time
Procedure B was invoked. It also would be unlikely that this would be the only occurrence of this type statement within your production code so the overhead would be significant and hence the resistance to allowing this level of information reaching production.

Here is the same package rewritten to take advantage of PL/SQL Conditional Compilation.
Other than the package name change, the only change is the replacement of the IF statement
in Procedure B with Conditional Compilation selection and inquiry directive. At this point the inquiry directive hasn’t been created.

CREATE PACKAGE pkg_with_cc
IS

   PROCEDURE a;

END pkg_with_cc;
/

CREATE OR REPLACE PACKAGE BODY pkg_with_cc
IS

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B');

      $IF $$debuging = TRUE
      $THEN 

         -- see what is in the collection
         FOR i IN 1 .. l_collection.COUNT()
         LOOP

            dbms_output.put_line('see the output of the collection'); 

         END LOOP; 

         -- view the xml
         dbms_output.put_line('the xml: ' || l_xml.getstringval() );

      $END  

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_with_cc;
/

Using dbms_preprocessor.print_post_processed_source to look at the compiled code


BEGIN

   dbms_preprocessor.print_post_processed_source
   (
      object_type => 'PACKAGE BODY',
      schema_name => USER,
      object_name => 'PKG_WITH_CC'
   );

END;
/

The output shows the debugging code has been stripped out by the PL/SQL preprocessor.


PACKAGE BODY pkg_with_cc
IS

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B'); 

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_with_cc;
/

Now lets recompile the package body, adding the inquiry directive, debugging at the same time.

ALTER PACKAGE PKG_WITH_CC COMPILE BODY
   PLSQL_CCFLAGS = 'debuging:TRUE' REUSE SETTINGS
/

The debugging literal matches $$debugging used by the procedure B and it is set to TRUE. I have included “REUSE SETTINGS” because it ensures that existing values for PL/SQL warnings or PL/SQL Optimizer Level are not discarded.

Running the dbms_preprocessor.print_post_processed_source and viewing the compiled version of the code we now see that the debugging code is present and ready to help solve the production issue.


...no changes to the other parts of the package

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B'); 

      -- see what is in the collection
      FOR i IN 1 .. l_collection.COUNT()
      LOOP

         dbms_output.put_line('see the output of the collection'); 

      END LOOP; 

      -- view the xml
      dbms_output.put_line('the xml: ' || l_xml.tostringval() );

 END b; 

...

Once finished we can remove the debugging code from production by recompiling the package body but this time setting the debugging flag to FALSE

ALTER PACKAGE PKG_WITH_CC COMPILE BODY
   PLSQL_CCFLAGS = 'debuging:FALSE' REUSE SETTINGS
/

Summary

Just to be clear, using Conditional Compilation as described in this post requires recompilation of production code which is not something that should ever been taken lightly and great care should be exercised whenever doing so.

Having the ability to leave debugging code in Production and enable it on demand, is in my opinion, reason enough to add PL/SQL Conditional Compilation to your developer toolbox. Used judiciously with an instrumented application it further reduces the number of places that bugs can hide.