Steven Feuerstein's Blog

Doing SQL in PL/SQL: key resource from Bryn Llewellyn

Steven Feuerstein — Wed, 08 Oct 2008 17:09:00 GMT

One of the highlights of Oracle Open World 2008 for me was the presentation by Bryn Llewellyn (PL/SQL Product Manager) on “Doing SQL in PL/SQL.”

Bryn surely has the most thorough and clear understanding of the PL/SQL language of anyone I have met (definitely including me).

His talk was detailed and precise (and maybe just a little bit overwhelming. He needed twice the time allotted) on this most important topic.

The bad news about this talk is that most PL/SQL developers will never be able to hear Bryn present it.

The good news is that everything he talks about (and more) may be found in a newly published whitepaper of the same name.

Simply visit the Oracle Technology Network PL/SQL Homepage and click on the hyperlink for that paper. Notice also that Bryn has published an important whitepaper on SQL injection.

Your understanding of PL/SQL will increase dramatically, as will your ability to write high quality PL/SQL programs, by reading both of these papers.

Thanks, Bryn!

For loops or While loops to scan collections?

Steven Feuerstein — Mon, 22 Sep 2008 16:21:00 GMT

I have generally recommended in the past that whenever you are writing code to iterate through the elements of a collection, you should use a while loop, combined with the FIRST-NEXT or LAST-PRIOR collection methods.

The key advantage of this approach is that the code will not raise a NO_DATA_FOUND exception if your collection is sparse (there is an index value between FIRST and LAST that is not defined). And if your collection is empty, the loop will not execute at all, whereas with a for loop, an empty collection could cause a VALUE_ERROR exception if you are not careful.

Here is an example of the kind of code that requires the use of the while loop:

DECLARE
   TYPEemployee_tt
   IS
      TABLEOFemployees%ROWTYPE
         INDEXBYpls_integer;

   employee_cache   employee_tt;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FORrecIN(SELECT   *
                 FROM   employees)
   LOOP
      employee_cache(rec.employee_id):=rec;
   ENDLOOP;

   FORindxIN1..employee_cache.COUNT
   LOOP
      DBMS_OUTPUT.put_line(employee_cache(indx).last_name);
   ENDLOOP;
END;
/

When I execute this block, I get the following error:

Instead, I should use a while loop:

DECLARE
   TYPEemployee_tt
   IS
      TABLEOFemployees%ROWTYPE
         INDEXBYpls_integer;

   employee_cache   employee_tt;
   l_index           pls_integer;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FORrecIN(SELECT   *
                 FROM   employees)
   LOOP
      employee_cache(rec.employee_id):=rec;
   ENDLOOP;

   l_index:=employee_cache.FIRST;

   WHILE(l_indexISNOTNULL)
   LOOP
      DBMS_OUTPUT.put_line(employee_cache(l_index).last_name);
      l_index:=employee_cache.NEXT(l_index);
   ENDLOOP;
END;
/

and then I will not see any errors.

So, no doubt about it, this is good advice – but should you always use the while loop?

I suggest that in situations when you know, when you are absolutely sure, that your collection is densely-filled, you should use the FOR loop, for two reasons:

It is a simpler solution, requiring less code (reducing the chance of a bug creeping into your code and making it easier to maintain).
It is more self-documenting. By using the for loop construct, you are stating that an assumption of this code is that the collection is densely-filled.
It is more efficient than the while loop.

So when you can be certain that your collection is densely-filled? When....

The collection is populated by a BULK COLLECT query. In this situation, the first index used is always 1, and the collection is filled sequentially (2, 3, 4...) from that index.
The collection is a nested table assigned its contents from a MULTISET operator (UNION, INTERSECT and EXCEPT). These set level operators always fill sequentially a nested table from index value 1.

And what kind of difference in performance can you expect to see? Not a very big difference, but the FOR loop is definitely faster. I put together the script below (relying on the sf_timer package, included in the demo.zip.

DECLARE
   l_index     pls_integer;
   l_source   DBMS_SQL.varchar2a;
BEGIN
   sf_timer.start_timer;

       SELECT    text
   BULKCOLLECTINTO   l_source
         FROM    all_source;

   sf_timer.show_elapsed_time(
      'Retrieved ' || TO_CHAR(l_source.COUNT) || ' elements'
   );
   --
   sf_timer.start_timer;

   FORindxIN1..l_source.COUNT
   LOOP
      NULL;
   ENDLOOP;

   sf_timer.show_elapsed_time('FOR loop through collection');
   --
   sf_timer.start_timer;
   l_index:=l_source.FIRST;

   WHILE(l_indexISNOTNULL)
   LOOP
      NULL;
      l_index:=l_source.NEXT(l_index);
   ENDLOOP;

   sf_timer.show_elapsed_time('Full collection scan with NEXT');
END;

I saw the following results:

Retrieved 3079394 elements - Elapsed CPU : 11.14 seconds.
FOR loop through collection - Elapsed CPU : .05 seconds.
Full collection scan with NEXT - Elapsed CPU : .48 seconds.

In other words, the while loop is an order of magnitude slower than the for loop. That sounds like a big deal, but it's only a difference of .4 seconds with a scan of over 3,000,000 elements. With smaller collections, you'll probably never notice the difference.

Analyzing code coverage with the PL/SQL profiler

Steven Feuerstein — Wed, 27 Aug 2008 22:55:00 GMT

I have, for the past several years, focused heavily on designing and building an automated code testing tool for PL/SQL: Quest Code Tester for Oracle. One feature that is often requested as a part of code testing is analysis of code coverage, answering questions like:

When I run my program do I use 50% of the code? 75% of the code?
Are there chunks of logic that are never run

The only way to get this kind of information is to turn on the PL/SQL profiler (and/or the new hierarchical profiler delivered with Oracle11g). The profiler keeps track of each line run by the program during the profile session. You then query the contents of the plsql_profiler_data|units|runs tables and draw your own conclusions.

And therein lies the rub.

Code Tester users don't want to see lengthy reports showing which lines where executed. Instead, they want us to provide them a single number that tells them all they need to know: "75% of my program was executed." Period.

And I would like to provide that feature, I really would. But I am feeling a little bit stumped and thought I would share with you what I have done so far, and my areas of befuddlement, in hopes that you, my dear reader, may be able to help provide additional clarity.

So what's the problem? Well....to figure out the % of code coverage, I need to....

decide which lines of code I should include in the total number of possible lines that could be executed. For example: do I include comments? What about the IS, BEGIN, END keywords?
understand what lines of the code the profiler actually pays attention to, and records as having been run. I have never found the profiler data to be entirely straightforward.

And so I have decided to do some research and exploration. I created a package that contains lines of code with all sorts of line breaks to see if I could isolate how Oracle treats such code in the context of profiling.

You will find below the code for this package, followed by a utility I wrote to show profiling data in a way that I thought would be useful, and then the output from a profiling session.

Which leads to my questions for you:

Do you have any ideas on how I should go about computing this single, golden number regarding code coverage?
What lines do you think should be included or not?
Should I take a different approach in analyzing code and profiler behavior?
What do you think of the output I got from my "test" package?

Looking forward to your comments,

Steven

Package to exercise profiler

CREATE OR REPLACE PACKAGE what_is_profiled
IS
   TYPE aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2);

   FUNCTION func1
      RETURN VARCHAR2;
      
      procedure driver ;
END what_is_profiled;
/

CREATE OR REPLACE PACKAGE BODY what_is_profiled
IS
   TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
      val
      INTEGER;
      condition1 boolean := true;
      condition2 boolean 
      := 
      true;
      
   BEGIN
      FOR indx IN 1 .. 100
      LOOP
         NULL;
      END LOOP;
            
      FOR 
      indx 
      IN 
      1 
      .. 
      100
      LOOP
         val := 1;
      END 
      LOOP; 
      
      FOR indx IN 1 .. 100 LOOP NULL; END LOOP;

      FOR rec IN (SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101)
      LOOP
         val := 1;
      END LOOP;

      FOR 
      rec 
      IN 
      (
      SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101
      )
      LOOP
         val := 1;
      END 
      LOOP;
      
      WHILE (condition1 AND condition2)
      LOOP
         condition1 := FALSE;
      END LOOP;

      WHILE 
      (
      condition1 
      AND 
      condition2
      )
      LOOP
         condition1 
         := 
         FALSE
         ;
      END LOOP;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT WHEN indx > 100;
            indx := indx + 1;
         END LOOP;
      END;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT 
            WHEN 
            indx 
            > 
            100;
            indx := indx + 
            1
            ;
         END LOOP;
      END;      
   END;

   PROCEDURE conditionals 
   IS
   a 
   boolean;
   b boolean;
   c boolean
   ;
   BEGIN
      IF (a AND b OR c)
      THEN
         NULL;
         elsif
         a
         then
         null;
         else
         dbms_output.put_line ('a');
      END IF;
      
      a := case
      true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;
      a := case true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;  
      
      case when 
      sysdate > sysdate + 1
      then
      a := false;
      when 1 > 2 then
      b := false;
      when 1
      > 2   
      then
      c := false;
      else null; end case; 
   END;

   FUNCTION p_func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN NULL;
   END;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
   BEGIN
      NULL;
   END;

   FUNCTION func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN p_func1;
   END;
   
   procedure driver is
   l varchar2(100);
   begin
   loops(1, l);
   conditionals;
   proc1
   (
   1
   ,
   l);
   GOTO checkloop;
   <>
   dbms_output.put_line ('a');
   end;
END what_is_profiled;
/

Utility to show profiler data

CREATE OR REPLACE PROCEDURE show_lines_profiled
/*
Assumptions: the ONLY data inside the profiler tables are for a single
run of what_is_profiled.driver
*/
IS
   l_type varchar2( 10000 );
BEGIN
   DBMS_OUTPUT.put_line( 'Profiling Report' );
   DBMS_OUTPUT.put_line( '  NOT PROFILED = No profile data for this line' );
   DBMS_OUTPUT.put_line( '  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0' );
   DBMS_OUTPUT.put_line( '  LINE RUN     = This line was executed at least once' );

   DBMS_OUTPUT.put_line( 'Profile Info   Line Source');
   DBMS_OUTPUT.put_line( 
'============== ==== ==============================================================');

   FOR rec
   IN (  SELECT line, text
           FROM all_source als
          WHERE     als.owner = USER
                AND als.name = 'WHAT_IS_PROFILED'
                AND als.TYPE = 'PACKAGE BODY'
       ORDER BY line )
   LOOP
      BEGIN
         SELECT CASE
                   WHEN total_occur = 0 THEN 'ZERO RUNS'
                   ELSE 'LINE RUN'
                END
                   profile_type
           INTO l_type
           FROM plsql_profiler_data ppd
          WHERE ppd.line# = rec.line AND ppd.unit_number = 2;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_type      := 'NOT PROFILED';
      END;

      DBMS_OUTPUT.put_line(   RPAD( l_type, 15 )
                           || LPAD( rec.line, 4 )
                           || ' '
                           || rtrim (rec.text, chr(10)));
   END LOOP;
END show_lines_profiled;

Results of profiling the package

BEGIN
   DELETE FROM plsql_profiler_data;

   DELETE FROM plsql_profiler_units;

   DELETE FROM plsql_profiler_runs;

   DBMS_OUTPUT.put_line(
                         DBMS_PROFILER.start_profiler( 'What is profiled?' )
   );
   what_is_profiled.driver( );
   DBMS_PROFILER.stop_profiler;
   --
   show_lines_profiled();
END;
/
Profiling Report
  NOT PROFILED = No profile data for this line
  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0
  LINE RUN     = This line was executed at least once
Profile Info   Line Source
============== ==== ==============================================================
NOT PROFILED      1 PACKAGE BODY what_is_profiled
NOT PROFILED      2 IS
NOT PROFILED      3    TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      4       INDEX BY PLS_INTEGER;
NOT PROFILED      5 
NOT PROFILED      6    TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      7       INDEX BY PLS_INTEGER;
NOT PROFILED      8 
ZERO RUNS         9    PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED     10    IS
NOT PROFILED     11       val
NOT PROFILED     12       INTEGER;
LINE RUN         13       condition1 boolean := true;
LINE RUN         14       condition2 boolean 
NOT PROFILED     15       := 
NOT PROFILED     16       true;
NOT PROFILED     17       
NOT PROFILED     18    BEGIN
LINE RUN         19       FOR indx IN 1 .. 100
NOT PROFILED     20       LOOP
LINE RUN         21          NULL;
NOT PROFILED     22       END LOOP;
NOT PROFILED     23             
LINE RUN         24       FOR 
NOT PROFILED     25       indx 
NOT PROFILED     26       IN 
NOT PROFILED     27       1 
NOT PROFILED     28       .. 
NOT PROFILED     29       100
NOT PROFILED     30       LOOP
LINE RUN         31          val := 1;
NOT PROFILED     32       END 
NOT PROFILED     33       LOOP; 
NOT PROFILED     34       
LINE RUN         35       FOR indx IN 1 .. 100 LOOP NULL; END LOOP;      
NOT PROFILED     36 
LINE RUN         37       FOR rec IN (SELECT *
NOT PROFILED     38                     FROM all_source
NOT PROFILED     39                    WHERE ROWNUM < 101)
NOT PROFILED     40       LOOP
LINE RUN         41          val := 1;
NOT PROFILED     42       END LOOP;
NOT PROFILED     43 
LINE RUN         44       FOR 
NOT PROFILED     45       rec 
NOT PROFILED     46       IN 
NOT PROFILED     47       (
ZERO RUNS        48       SELECT *
NOT PROFILED     49                     FROM all_source
NOT PROFILED     50                    WHERE ROWNUM < 101
NOT PROFILED     51       )
NOT PROFILED     52       LOOP
LINE RUN         53          val := 1;
NOT PROFILED     54       END 
NOT PROFILED     55       LOOP;
NOT PROFILED     56       
LINE RUN         57       WHILE (condition1 AND condition2)
NOT PROFILED     58       LOOP
NOT PROFILED     59          condition1 := FALSE;
NOT PROFILED     60       END LOOP;
NOT PROFILED     61 
NOT PROFILED     62       WHILE 
NOT PROFILED     63       (
NOT PROFILED     64       condition1 
NOT PROFILED     65       AND 
NOT PROFILED     66       condition2
NOT PROFILED     67       )
NOT PROFILED     68       LOOP
NOT PROFILED     69          condition1 
NOT PROFILED     70          := 
NOT PROFILED     71          FALSE
NOT PROFILED     72          ;
NOT PROFILED     73       END LOOP;
NOT PROFILED     74       
NOT PROFILED     75       DECLARE
LINE RUN         76          indx   INTEGER := 1;
NOT PROFILED     77       BEGIN
LINE RUN         78          LOOP
LINE RUN         79             EXIT WHEN indx > 100;
LINE RUN         80             indx := indx + 1;
NOT PROFILED     81          END LOOP;
NOT PROFILED     82       END;
NOT PROFILED     83       
NOT PROFILED     84       DECLARE
LINE RUN         85          indx   INTEGER := 1;
NOT PROFILED     86       BEGIN
LINE RUN         87          LOOP
LINE RUN         88             EXIT 
NOT PROFILED     89             WHEN 
NOT PROFILED     90             indx 
NOT PROFILED     91             > 
NOT PROFILED     92             100;
LINE RUN         93             indx := indx + 
NOT PROFILED     94             1
NOT PROFILED     95             ;
NOT PROFILED     96          END LOOP;
NOT PROFILED     97       END;      
LINE RUN         98    END;
NOT PROFILED     99 
ZERO RUNS       100    PROCEDURE conditionals 
NOT PROFILED    101    IS
NOT PROFILED    102    a 
NOT PROFILED    103    boolean;
NOT PROFILED    104    b boolean;
NOT PROFILED    105    c boolean
NOT PROFILED    106    ;
NOT PROFILED    107    BEGIN
LINE RUN        108       IF (a AND b OR c)
NOT PROFILED    109       THEN
NOT PROFILED    110          NULL;
NOT PROFILED    111          elsif
LINE RUN        112          a
NOT PROFILED    113          then
NOT PROFILED    114          null;
NOT PROFILED    115          else
LINE RUN        116          dbms_output.put_line ('a');
NOT PROFILED    117       END IF;
NOT PROFILED    118       
LINE RUN        119       a := case
NOT PROFILED    120       true
NOT PROFILED    121       when true
NOT PROFILED    122       then
NOT PROFILED    123       false
NOT PROFILED    124       when 
NOT PROFILED    125       false then
NOT PROFILED    126       true
NOT PROFILED    127       else
NOT PROFILED    128       false
NOT PROFILED    129       end
NOT PROFILED    130       ;
LINE RUN        131       a := case true
NOT PROFILED    132       when true
NOT PROFILED    133       then
NOT PROFILED    134       false
NOT PROFILED    135       when 
NOT PROFILED    136       false then
NOT PROFILED    137       true
NOT PROFILED    138       else
NOT PROFILED    139       false
NOT PROFILED    140       end
NOT PROFILED    141       ;  
NOT PROFILED    142       
LINE RUN        143       case when 
NOT PROFILED    144       sysdate > sysdate + 1
NOT PROFILED    145       then
LINE RUN        146       a := false;
NOT PROFILED    147       when 1 > 2 then
NOT PROFILED    148       b := false;
NOT PROFILED    149       when 1
NOT PROFILED    150       > 2   
NOT PROFILED    151       then
NOT PROFILED    152       c := false;
NOT PROFILED    153       else null; end case; 
NOT PROFILED    154    END;
NOT PROFILED    155 
ZERO RUNS       156    FUNCTION p_func1
NOT PROFILED    157       RETURN VARCHAR2
NOT PROFILED    158    IS
NOT PROFILED    159    BEGIN
ZERO RUNS       160       RETURN NULL;
ZERO RUNS       161    END;
NOT PROFILED    162 
ZERO RUNS       163    PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED    164    IS
NOT PROFILED    165    BEGIN
LINE RUN        166       NULL;
NOT PROFILED    167    END;
NOT PROFILED    168 
ZERO RUNS       169    FUNCTION func1
NOT PROFILED    170       RETURN VARCHAR2
NOT PROFILED    171    IS
NOT PROFILED    172    BEGIN
ZERO RUNS       173       RETURN p_func1;
ZERO RUNS       174    END;
NOT PROFILED    175    
ZERO RUNS       176    procedure driver is
NOT PROFILED    177    l varchar2(100);
NOT PROFILED    178    begin
LINE RUN        179    loops(1, l);
LINE RUN        180    conditionals;
LINE RUN        181    proc1
NOT PROFILED    182    (
NOT PROFILED    183    1
NOT PROFILED    184    ,
NOT PROFILED    185    l);
LINE RUN        186    GOTO checkloop;
NOT PROFILED    187    <>
LINE RUN        188    dbms_output.put_line ('a');
LINE RUN        189    end;
NOT PROFILED    190 END what_is_profiled;

Oracle Open World Presentations

Steven Feuerstein — Tue, 22 Jul 2008 15:41:00 GMT

Hey folks,

For anyone attending Oracle Open World this year, here is my schedule of presentations:

Session ID: S300184
Session Title: Weird PL/SQL
Track: Oracle Develop: Database
Room: Golden Gate C3
Date: 2008-09-21
Start Time: 15:45

Session ID: S300183
Session Title: Break Your Addiction to SQL!
Track: Oracle Develop: Database
Room: Salon 02
Date: 2008-09-22
Start Time: 13:00

Session ID: S300185
Session Title: Why You Should Care About Oracle 11g PL/SQL Now
Track: Oracle Develop: Database
Room: Salon 02
Date: 2008-09-23
Start Time: 11:30

Hope to see you there!

Always Bulk Collect

Steven Feuerstein — Mon, 23 Jun 2008 14:34:00 GMT

You learn something new every day, right? Well, I certainly do (more or less). Even about PL/SQL, about which I am sure many people think I already know everything. Far from it.

In fact, I learned just last week from the PL/SQL Product Manager, Bryn Llewellyn, that his recommendation regarding cursor FOR loops and bulk collect is different from mine – and for a very good reason.

Several years ago, he informed me (and provided a script to verify the statement: 10g_optimize_cfl.sql in the demo.zip) that in Oracle10g, the compiler now optimizes every cursor FOR loop to execute at a level of performance similar to BULK COLLECT.

This is a wonderful and non-trivial optimization. Rather than have to go through a manual rewrite of all cursor FOR loops, you can leave them in place and reap the benefits of the BULK COLLECT (sort of).

Well, that's not quite true – and it's even less true than I thought.

First of all, if your cursor FOR loop contains any DML statements, you will still want to explicitly convert to BULK COLLECT. The reason is that while the optimizer will improve the performance of the query step, it will not optimize the DML statements to execute like FORALLs (the bulk processing analogue for updates, inserts and deletes).

So if you have code that looks like (cfl_to_bulk_0.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   CURSOR emp_cur
   IS
      SELECT employee_id, salary, hire_date
        FROM employees
       WHERE department_id = dept_in;
BEGIN
   FOR rec IN emp_cur
   LOOP
      BEGIN
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (rec.employee_id, rec.salary, rec.hire_date
                     );

         adjust_compensation (rec.employee_id, rec.salary);

         UPDATE employees
            SET salary = newsal_in
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            log_error;
      END;
   END LOOP;
END upd_for_dept;

You will need to change the cursor FOR loop to BULK COLLECT so that you can fill up collections with data from the query, which can then be used in the FORALL statement. You will, sadly (due to increased complexity and program length) end up with something like this (cfl_to_bulk_5.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   bulk_errors    EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE employee_tt IS TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;

   employee_ids   employee_tt;

   TYPE salary_tt IS TABLE OF employees.salary%TYPE
     INDEX BY BINARY_INTEGER;

   salaries       salary_tt;

   TYPE hire_date_tt IS TABLE OF employees.hire_date%TYPE
      INDEX BY BINARY_INTEGER;

   hire_dates     hire_date_tt;

   CURSOR employees_cur
   IS
      SELECT     employee_id, salary, hire_date
            FROM employees
           WHERE department_id = dept_in
      FOR UPDATE;

   PROCEDURE fetch_data_quickly (
      limit_in           IN       PLS_INTEGER
    , employee_ids_out   OUT      employee_tt
    , salaries_out       OUT      salary_tt
    , hire_dates_out     OUT      hire_date_tt
   )
   IS
   BEGIN
      FETCH employees_cur
      BULK COLLECT INTO employee_ids_out, salaries_out, hire_dates_out LIMIT limit_in;
   END fetch_data_quickly;

   PROCEDURE adj_comp_for_arrays (
      employee_ids_io   IN OUT   employee_tt
    , salaries_io       IN OUT   salary_tt
   )
   IS
   BEGIN
      FOR indx IN 1 .. employee_ids_io.COUNT
      LOOP
         adjust_compensation (employee_ids_io (indx), salaries_io (indx));
      END LOOP;
   END adj_comp_for_arrays;

   PROCEDURE insert_history
   IS
   BEGIN
      FORALL indx IN employee_ids.FIRST .. employee_ids.LAST SAVE EXCEPTIONS
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (employee_ids (indx), salaries (indx), hire_dates (indx)
                     );
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            -- Log the error
            log_error
                     (   'Unable to insert history row for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
            /*
            Communicate this failure to the update phase:
            Delete this row so that the update will not take place.
            */
            employee_ids.DELETE (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
         END LOOP;
   END insert_history;

   PROCEDURE update_employee
   IS
   BEGIN
      /*
        Use Oracle10g INDICES OF to avoid errors
        from a sparsely-populated employee_ids collection.
      */
      FORALL indx IN INDICES OF employee_ids SAVE EXCEPTIONS
         UPDATE employees
            SET salary = newsal_in
              , hire_date = hire_dates (indx)
          WHERE employee_id = employee_ids (indx);
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            log_error
                     (   'Unable to update salary for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
         END LOOP;
   END update_employee;
BEGIN
   OPEN employees_cur;

   LOOP
      fetch_data_quickly (100, employee_ids, salaries, hire_dates);
      EXIT WHEN employee_ids.COUNT = 0;
      insert_history;
      adj_comp_for_arrays (employee_ids, salaries);
      update_employee;
   END LOOP;
END upd_for_dept;

Sorry, I know that I should probably explain this code but to be brutally honest my dad had heart bypass surgery and I am currently at the hospital and so you get the abbreviated version ....

Anyway, that is one scenario where you definitely need to convert from the cursor FOR loop.

But Bryn, to my surprise, recommends that you always convert explicitly to BULK COLLECT. Why is that? For performance reasons. Apparently, the cursor FOR loop optimization makes the code run faster, but not as quickly as BULK COLLECT.

In my tests (which you can reproduce in cfl_vs_bulkcollect.sql), I found that BULK COLLECT ran 33% faster than the cursor FOR loop:

Cursor For Loop Elapsed: 8.12 seconds.
Bulk Collect Elapsed: 5.46 seconds.

So if you really want the best performance, take the time and make the effort to switch over to BULK COLLECT.

How to Run an OS Command from PL/SQL

Steven Feuerstein — Wed, 04 Jun 2008 14:11:00 GMT

Oracle doesn't make it terribly easy to run operating system commands from within a PL/SQL block. I suppose that's understandable, given that PL/SQL is an embedded database-oriented language. Still, developers do ask me on a regular basis about how they can do this.

As I understand it, there are basically three ways to do this:

Invoke a Java method from within a PL/SQL wrapper
Call a C program as an external procedure from within PL/SQL.
Use the new DBMS_SCHEDULER package.

I will soon publish a DBMS_SCHEDULER solution (written by Bryn Llewellyn, PL/SQL Product Manager) on my OTN Best Practices column. In the meantime, you will find below a quick review of the steps needed to do this in Java and C.

Executing Host Command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

1. Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.

2. Build a class that invokes that host command method.

Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;
public class execHostCommand
{ 
  public static void execute (String command) 
    throws java.io.IOException
  {
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
  }
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.

3. Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
  PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
           'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.

4. Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges is to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing Host Command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, read Chapter 27 of Oracle PL/SQL Programming, 4th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help from your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

1. Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
   return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.

2. Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll

3. Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".

4. Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib 
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix
     
CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle's CREATE LIBRARY privilege.

5. Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);

Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Testing and refreshing data from production

Steven Feuerstein — Thu, 17 Apr 2008 16:21:00 GMT

As many of my readers likely know by now, I have been working for the past several years on the Quest Code Tester development effort.

Code Tester is the most powerful PL/SQL test automation tool available. You describe the expected behavior of your programs and Code Tester generates your test code, which can then be run from the UI or via a script. With Code Tester, you can build comprehensive regression tests and even implement the Test Driven Development methodology.

In the process of talking about Code Tester with many developers, I have come across a belief regarding code testing and the refreshing of data from production tables that I think actually reflects a misunderstanding about both how to use Code Tester specifically and, more generally, how to test our code.

Here's the way this belief was expressed by a customer recently:

"We have a development environment - let's call it TEST. Inside this environment is the supporting schema / repository for Code Tester, as well as the test definitions and generated test code."

"Once a week, we refresh the TEST environment from the production environment, PROD. PROD doesn't contain a Code Tester repository, so after I refresh, I lose my test repository and code."

Clearly, this developer can do an export of the Code Tester schema and then import it after refresh, but he was concerned about having to add any overhead for the DBA to this refresh process.

Actually, I think the problem goes much deeper than that: if you refresh your test tables with production data on a regular basis, you will find it very difficult indeed to create stable regression tests that can be run against your code.

Why do I say that? Because when you refresh data from production, you change the "inputs" to your programs (contents of the tables) and therefore you will almost certainly have to change the expected results for your tests.

In fact, I think that when it comes to functional testing of your programs (does it meet user requirements?), you should not be refreshing your test tables from production. To understand why I would say this, let's talk about....

How we make sure our programs work

There are as many kinds of tests as there are definitions of what it means for a program to "work." We need to make sure, for example, that our programs meet functional requirements (they are correct) and also that they run quickly enough to avoid user frustration. The programs need to scale up for many users and lots of data, etc.

Quest Code Tester is designed specifically to help you implement tests on functional requirements; in essence, to verify that your program is correct.

To do this, I will almost always want to compare the actual results of running my program with the expected results or control data.

For example, if table XYZ contains a certain set of rows, then after running the program, table ABC should be changed in a specific way. Or the program is a function such that when I pass "ABC" for an IN argument, the function returns 100, and so on.

In general, there is no room for ambiguity here. Either the program works as expected or it does not.

Now, if I want to build regression tests and automate the process of testing my program, I need to able to tell Code Tester that for a given set of inputs, I expect the associated outcomes. And – this is the key thing to realize – those inputs can't keep changing on me. Every time the inputs change, I would need to change the expected outcomes.

Constantly updating one's test code might make sense if you are perform manual tests from hand-coded scripts. But if you want to build comprehensive, serious regression tests, then you need a stable, consistent environment from which to run those tests.

Every time you refresh data from production, you change the values in your table and thus you cannot reliably execute your regression test.

But don't we need real production data to really test?

Is this a big problem? Does this mean that we can't or shouldn't build static, repeatable regression tests for our code? After all (so the thinking goes), we need to test our code against production data to make sure that code handles "real world" situations.

Clearly, our code does need to work properly with production data.

This does not mean, however, that you need to constantly change the data for your functional tests as production data changes. It does mean that the data used in functional tests should represent the variety of data found in production.

In other words, the data in your test tables must be sufficiently varied to allow you to exercise the program to verify all requirements. It doesn't really matter so much that the data is precisely the same as that found in production.

And, again, if you keep changing the test data, you must also change your test definition.

OK, but don’t we need to test against production volume?

There's another problem with basing functionality testing around production data: the data volumes are generally too large, increasing the time it takes to complete the tests.

You certainly do need to make sure your code executes with production volumes of data. But that sort of stress testing should be done independently of your functionality testing. With functionality testing, you want your tests to run as quickly as possible, for these reasons:

You will have lots of (dozens, perhaps hundreds) of separate test cases to run; if each test takes five minutes due to data volume, the test cycle will take an enormous amount of time.
Ideally, you run your tests after each change you make to your program. That way, you can immediately determine if you have introduced any bugs. But if running those tests takes an hour, you will test less frequently and you will get less "return" on your investment of creating your tests.

Conclusion: Segregate your functional test environment

The most important thing to verify about your program is that it is correct: it meets user requirements.

The best way to do this is to build a regression test that you can run after any change to the program, to ensure that it has no bugs.

A regression test should not have to be changed as long as the program itself has not changed. It should work from a consistent set of "inputs" (values for IN arguments, contents of any tables queried by programs, etc.) that do not change. [Of course, you may need to make some changes along the way as program requirements change, as you add more test cases, etc. That is different, however, from daily or weekly refreshes.]

In addition, you want regression tests to run as efficiently as possible. These tests focus on program functionality, not performance. So you want the minimum volume of data in test tables that allow you to cover your requirements. Use different tests to verify adequate performance.

Consequently, when it comes to functionality testing, you should avoid refreshing your test tables from production. Instead, invest the time upfront to come up with setup scripts to populate tables with data that fully exercises your code. Include those setup scripts in your Code Tester test definitions and then you have an independent, consistent test environment.

Auto-formatting of templates for Toad

Steven Feuerstein — Thu, 10 Apr 2008 16:14:00 GMT

A few months ago, I posted on this blog an explanation of how to use Toad's Code Templates to standardize development and improve productivity. I included an XML document that contains over 20 templates that I thought you might find useful. I also asked my readers to produce XML transformations so that the XML document could be "output" in the format that Toad recognizes (and SQL Navigator as well).

I am happy to say that Sean Gilbert has done this for Toad 8 and is working on a transformation for Toad 9.

For more information, check out https://getonthetrail.blogsite.org/Blog/post/2008/04/Transform-Custom-TOAD-XML-Template-with-XSLT.aspx.

Thanks, Sean!

Weird PL/SQL

Steven Feuerstein — Mon, 17 Mar 2008 19:57:00 GMT

Collaborate08, annual conference of the International Oracle User Group, and several other national and international user groups, will be held in Denver this year, from April 13th to the 16^th. I am going to present three papers, including (for the first time) Weird PL/SQL. I thought you might enjoy reading about some of the weirdnesses of PL/SQL in my ToadWorld blog, so here's an excerpt from the beginning of my whitepaper:

Introduction

You probably think that PL/SQL is rather ordinary programming language. Well, it truly is a very powerful and straightforward language, but there are some features "less traveled" which can seem nothing less than weird. This presentation explores some of stranger nooks and crannies of the PL/SQL language, perhaps in the process making them a little bit less weird to the everyday programmer.

All the code I reference in this paper is available at my PL/SQL Obsession site: www.ToadWorld.com/SF. Just click on the "Trainings" link and then click on the "demo.zip" link.

Disclosure: I will poke fun at Oracle, the PL/SQL language, and implicitly the folks who are responsible for building and enhancing PL/SQL. Please know that it is all done in a spirit of deep appreciation for what PL/SQL (and its developers) has done for me and so many others around the world. But, hey, there's always room for improvement and you've got to keep a sense of humor about all this stuff!

Error Codes: Negative or Positive? Make up your mind, Oracle!

I really would have thought that Oracle would have sorted this out by now. Here's the issue:

When you see this string:

ORA-01855

do you interpret the "-" character as a hyphen or a negative sign?

I always considered it to be a negative sign, and that just about every single error code in the world of Oracle is negative. The only exceptions are 1 (user-defined exception) and 100 ("No data found" – which is another little weirdness in PL/SQL. The "No data found" exception has two error codes: 100 and -1403).

Yet sometimes Oracle treats the error codes as positive numbers, most notably when using the SAVE EXCEPTIONS clause of FORALL. When you include SAVE EXCEPTIONS, Oracle "saves up" any exceptions it encounters as it executes all the DML statements specified by the bind array. Then if at least one error occurred, it raises the ORA-24381 exception (Argh! Sometimes I worry about all the little bits of information that are stuck in my head!). It also populates the SQL%BULK_EXCEPTIONS pseudo-collection with all the exceptions that were raised.

Here's an example of a program that uses this feature:

/* bulkexc.sql */
DECLARE
   bulk_errors          EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE namelist_t IS TABLE OF VARCHAR2 (1000);

   enames_with_errors   namelist_t
      := namelist_t ('ABC'
                   , 'DEF'
                   , NULL
                   , 'LITTLE'
                   , RPAD ('BIGBIGGERBIGGEST', 250, 'ABC')
                   , 'SMITHIE'
                    );
BEGIN
   FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST SAVE EXCEPTIONS
      UPDATE employees
         SET last_name = enames_with_errors (indx);
   ROLLBACK;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (   'Error '
                               || indx
                               || ' occurred on index '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                               || ' with error code '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                              );
      END LOOP;

      ROLLBACK;
END;
/

And this is what I see when I run the program:

Error 1 occurred on index 3 with error code 1407
Error 2 occurred on index 5 with error code 12899

Hmmm. Oracle returns the error code as a positive number! So if I want to use SQLERRM to look up the error message for that code, I have to multiply it by -1:

FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
   DBMS_OUTPUT.put_line
                      (   'Error '
                       || indx
                       || ' occurred on index '
                       || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                       || ' with error '
                       || SQLERRM
                                ( -1
                                 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                                )
                      );
END LOOP;

And then I see this output:

Error 1 occurred on index 3 with error ORA-01407: cannot update () to NULL
Error 2 occurred on index 5 with error ORA-12899: value too large for column

It would be nice if Larry would decide once and for all if error codes are positive or negative, and then lay down the law.

SQLERRM and DBMS_OUTPUT.PUT_LINE

Speaking of SQLERRM....let's talk about everybody's favorite program: DBMS_OUTPUT.PUT_LINE. For many, many years – all the way up to the release of Oracle Database 10g Release 2, if you tried to display a string of more than 255 characters with DBMS_OUTPUT.PUT_LINE, that built-in would raise an exception. This caused no end of teeth-gnashing and anguished moans from developers over the year. Fortunately, that restriction was lifted in Oracle Database 10g Release 2 – you can now display up to 32K characters.

But there is still a problem with SQLERRM. We all know about SQLERRM. You call it to return the error message for the current error (obtained by calling SQLCODE). Did you know, however, that Oracle recommends that you not use this function,. and instead call DBMS_UTILITY.FORMAT_ERROR_STACK?

Why would that be? The problem is that SQLERRM may truncate your error message. In earlier versions of Oracle, truncated occurred at 255 characters. Now, it is 512. DBMS_UTILITY.FORMAT_ERROR_STACK, on the other hand, returns strings of up to 2000 bytes.

I can just picture some developer years and years ago, confronted with a nasty problem:

He has found that SQLERRM can return error messages that get quite long, depending on application-specific information, like the names of identifiers. And when you try to display that string with DBMS_OUTPUT.PUT_LINE, an exception is raised.

What's a developer to do?

1. Fix DBMS_OUTPUT.PUT_LINE so it displays longer strings.

or

2. Truncate your error message.

Well, obviously, the solution is to truncate the error message!

Isn't that weird?

I am so glad the PL/SQL team finally got around to fixing DBMS_OUTPUT.PUT_LINE! And according to the PL/SQL development manager at the time, all they had to was change one number!

So...there's a little bit of weirdness in PL/SQL...more to come at Collaborate08 and this blog.

Just how popular is PL/SQL?

Steven Feuerstein — Mon, 04 Feb 2008 18:56:00 GMT

Just how popular is PL/SQL?

And how many PL/SQL developers are there "out there"?

These are surprisingly hard questions to answer. Officially and even unofficially, Oracle Corporation's point people on PL/SQL do not have any idea (or refuse to say) how many PL/SQL developers there are.

I take the approach of doing some rough extrapolations from numbers of books I and other PL/SQL developers have sold, and I conclude that there are perhaps a couple million PL/SQL developers, all told.

That is, however, very unscientific.

There are some websites, however, that offer an analysis of relative popularity of languages, usually based on data they have retrieved from various search sites.

TIOBE

TIOBE compiles a list of relative popularity of programming languages at http://www.tiobe.com/index.htm?tiobe_index.

Here is the list as of January 2008:

Ok, fine, so PL/SQL is not the most popular language. We all know that. What I was very pleased to see is that PL/SQL had increased in popularity over the last year. Based on my experience in India, I can see why; most of the increase probably came from there alone!

Here is some information from the TIOBE website about how they calculate their ratings:

The ratings are calculated by counting hits of the most popular search engines. The search query that is used is: +" programming"
The search query is executed for the regular Google, Google Blogs, MSN, Yahoo!, and YouTube web search for the last 12 months. The web site Alexa.com has been used to determine the most popular search engines.
The number of hits determine the ratings of a language. The counted hits are normalized for each search engine for the first 50 languages. In other words, the first 50 languages together have a score of 100%.

Let's define "hits50(SE)" as the sum of the number of hits for the first 50 languages for search engine SE and "hits(PL,SE)" as the number of hits for programming language PL for search engine SE, then the formal definition of the ratings becomes

((hits(PL,SE1)/hits50(SE1) + ... + hits(PL,SEn)/hits50(SEn))/n
 where n is the number of search engines used.

LangPop

The nice thing about the TIOBE site is that they rank PL/SQL explicitly. All the other language popularity pages I could find simply list "SQL," which I took to mean "SQL and all procedural language extensions to SQL."

The http://www.langpop.com/ site shows the following graph:

O'Reilly Media Popularity by Book Sales

O'Reilly Media, publisher of all my books on PL/SQL, has a research arm ("O'Reilly Radar") that projects programming language trends based on book sales. Again, there is just a single entry for SQL:

http://radar.oreilly.com/archives/2005/04/book_sales_as_a.html