Monthly Archives: January 2013

Selecting many rows when working with PL/SQL

In this article I will explain the method I use when I need to process an indeterminate number of rows using PL/SQL.

I became aware of this method after reading Bryn Llewellyn’s white paper Doing SQL From PL/SQL: Best and Worst Practises (My example is based on the code shown on page 31 of the white paper)

The code is shown below and I will explain how it works:

DECLARE

   CURSOR employees_cur IS
      SELECT e.employee_id,
             e.first_name,
             e.last_name,
             e.salary
      FROM employees e;

   TYPE emp_type IS TABLE OF employees_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   laa_emps emp_type;

   lkn_batchsize CONSTANT NUMBER(3) := 100;

BEGIN

   OPEN employees_cur;

   LOOP

      FETCH employees_cur BULK COLLECT INTO laa_emps LIMIT lkn_batchsize;

      FOR i IN 1 .. laa_emps.COUNT()
      LOOP

         -- do something interesting with each employee record
         null;

      END LOOP;

      EXIT WHEN laa_emps.COUNT() < lkn_batchsize;

   END LOOP;

   CLOSE employees_cur;

END;

First of all I define an explicit cursor to select the records that I want to work with. (lines 3 – 8). The employee table is a copy of the standard Oracle HR.EMPLOYEE table which contains 107 records.

I then create a PL/SQL Associative Array based on the explicit cursor defined at (lines 3 – 8) and declare a variable of this type. (lines 10 – 13)

The next line declares a constant that controls the batch size or the number of records that each fetch will return. (line 15)

The cursor is opened. (line 19)

A basic loop is entered. (line 21) I will refer to this loop as the outer loop

Using the bulk collect construct, the first batch of 100 records are fetched from the cursor into the associative array variable. (line 23).

The for loop is entered  (line 25) .  It is within this loop that the processing on each record is performed (line 28). This loop is executed 100 times.

Once the batch of records have been processed,  the next to be executed is line 33 where an evaluation is made whether to exit the outer loop. If the count of records that has just been fetched is less than the batch size then there are no further records to process. In this first iteration, the number of records retrieved was 100 and the batch size is 100 so this evaluates to false so processing returns to line 23.

This time the bulk collect fetches the remaining 7 records into the associative array variable. (line 23)

The for loop is again entered and the loop is executed 7 times. (line 28)

With the for loop complete the evaluation at (line 33) is again executed and this time evaluates to TRUE. The count of records is 7 which is less than the batch size of 100 which means all the records have been processed. The outer loop is then exited.

Finally the cursor is closed and the end of the program is reached. (line 37)

What has changed?

“It was working yesterday…”

Working in any form of shared development environment you will sooner or later hit a problem when something that used to work has either stopped working or is doing something it wasn’t doing before.

So how do you find out what have changed? With regards PL/SQL objects such as Packages, Triggers etc. the answer can be found by querying the Oracle Data Dictionary.

In the November/December 2012 issue of Oracle Magazine, Steven Feuerstein’s article on PL/SQL  101 describes the information that is available to developers via the various Data Dictionary views and included in that article is a SQL query that can be used to display objects belonging to the user that have changed today:

SELECT object_type,
       object_name,
       last_ddl_time
FROM   user_objects
WHERE  last_ddl_time >= TRUNC(SYSDATE)
ORDER BY object_type,
         object_name

Running this query in SQL Developer shows that the ADD_EMPLOYEE procedure has changed or has it?

What has changed

One limitation with this approach is that the query relies on the last_ddl_time column. According to the Oracle documenation this column…

“is the last modification of the object resulting from a DDL statement (including grants and revokes)”.

So this column is not set just by code changes. For the ADD_EMPLOYEE example I didn’t make any changes to the procedure code but I did recompile it, which changed the value for the last_ddl_time column and so it appeared when I ran the query against user_objects.

This limitation aside,  I have found this to be a useful query. Thanks Steven!