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)

Oracle File Watcher on a Windows PC

Introduction

Introduced in Oracle 11g Release 2, the File Watcher enables jobs to be triggered when a file arrives in an Operating System Folder.

In this article I am going to set up a new file watcher on my Windows PC. The example inserts the contents of the newly arrived file into a database table.  The information shown here is distilled from the Oracle documentation

Before getting into the detail, here is a quick run down of the key components and their versions that was used to create the example.

  1. Microsoft Windows XP with Service Pack 3 running via Oracle Virtual Box
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
  3. Oracle SQL Developer 3.2.10.09
  4. The user running all the code is logged in with the DBA role

Step 1 Alter the File Watcher Interval (Optional)

File watchers check for the arrival of files every ten minutes by default. If you want to change the interval, connect as sys user and run the set_attribute procedure of dbms_scheduler.

The following example changes the interval to one minute.

BEGIN

   dbms_scheduler.set_attribute
     ('file_watcher_schedule',
      'repeat_interval',
      'freq=minutely; interval=1'
   );

END;
/

Step 2 Create a credential

In order for the File Watcher to be able to access the file(s) on Windows, a  Scheduler credential object is required. The following code creates a credential called “watch_credential”

BEGIN

   dbms_scheduler.create_credential
   (
      credential_name => 'watch_credential',
      username        => 'your operating system username',
      password        => 'your operating system password',
   );

END;
/

Step 3 File Location Details

The call to create_file_watcher (unsurprisingly) creates the file watcher object and tells it where to “watch” for incoming files along with the name of the file that you are interested in.

In the following example I want the File Watcher to watch for files that appear in the Operating System directory C:etl_dir and as the names of the files could be all different but will have the .txt suffix I have set the file name parameter accordingly.

BEGIN

   dbms_scheduler.create_file_watcher
   (
      file_watcher_name => 'the_file_watcher',
      directory_path    => 'C:etl_dir',
      file_name         => '*.txt',
      credential_name   => 'watch_credential',
      destination       => NULL,
      enabled           => FALSE
   );

END;
/

Step 4 Specify the program unit that will be executed when the file watcher runs

In this step I have specified that the stored procedure that will be executed by the File Watcher, when the file arrives. The stored procedure, sp_load_customer_files, doesn’t yet exist and will be created in Step 6.

BEGIN

   dbms_scheduler.create_program
   (
      program_name        => 'file_watcher_prog',
      program_type        => 'stored_procedure',
      program_action      => 'sp_load_customer_files',
      number_of_arguments => 1,
      enabled             => FALSE
   );
END;
/

Step 5 Defining metadata

In order for the new stored procedure, sp_load_customer_files, to access attributes of event that started the File Watcher, a call to dbms_scheduler.define_metadata_argument is required.

For more information about this program unit please refer to the documentation.

BEGIN

   dbms_scheduler.define_metadata_argument
   (
      program_name       => 'file_watcher_prog',
      metadata_attribute => 'event_message',
      argument_position  => 1
   );

END;
/

Step 6 Creating the supporting objects

This step creates a table where the contents of the files will be inserted into, along with the file name. To keep the example concise, no primary keys, indexes etc have been defined.

CREATE TABLE files_from_customers(file_name     VARCHAR2(100),
                                  file_contents CLOB);

The stored procedure that was first referenced in step 4 is now created. This procedure uses some attributes from the filewatcher object to obtain the file name. It then uses the dbms_lob packages to load the data from the file into the table.

CREATE OR REPLACE PROCEDURE sp_load_customer_files
(pt_payload IN sys.scheduler_filewatcher_result)
IS

 lc_clob           CLOB;
 lt_bfile          BFILE;
 li_warning        INTEGER;
 li_dest_offset    INTEGER := 1;
 li_src_offset     INTEGER := 1;
 li_lang_context   INTEGER := 0;

BEGIN
   INSERT INTO files_from_customers (file_name,
                                     file_contents)
   VALUES(
   pt_payload.directory_path || '' || pt_payload.actual_file_name,
   empty_clob())
   RETURNING file_contents INTO lc_clob;
   lt_bfile := BFILENAME(directory => 'ETL_DIR',
                         filename  => pt_payload.actual_file_name);

   dbms_lob.fileopen
   (
      file_loc => lt_bfile
   );

   dbms_lob.loadclobfromfile
   (
      dest_lob     => lc_clob,
      src_bfile    => lt_bfile,
      amount       => dbms_lob.getlength(file_loc =&gt; lt_bfile),
      dest_offset  => li_dest_offset,
      src_offset   => li_src_offset,
      bfile_csid   => NLS_CHARSET_ID('UTF8'),
      lang_context => li_lang_context,
      warning      =>; li_warning
   );

  dbms_lob.fileclose
  (
     file_loc => lt_bfile
  );

END sp_load_customer_files
/

Step 7: Creating a job

Create an Event-Based Job That References the File Watcher.

BEGIN

   dbms_scheduler.create_job
   (
      job_name        => 'file_watcher_job',
      program_name    => 'file_watcher_prog',
      event_condition => NULL,
      queue_spec      => 'the_file_watcher',
      auto_drop       => FALSE,
      enabled         => FALSE
    );

END;
/

Step 8: Enable All the objects

Enable all the objects that you have created by running:

BEGIN

   dbms_scheduler.enable
   (
      'the_file_watcher, file_watcher_prog, file_watcher_job'
   );

END;
/

Step 9: Seeing the results

Before a file arrives, query the table to show it is empty:

A file arrives into the directory that the File Watcher is monitoring.

When the File Watcher runs (as specified in Step 1) the contents of the new file are inserted into the table:

Step 10 Nothing has happened! (Optional)

So you have followed all the steps shown, double checked the code and nothing has happened. The file hasn’t loaded and your table is still empty.  You have my sympathy! As with anything with many moving parts something is bound not to work.

One tool I found invaluable in debugging these issues is looking at the run log for the job (This is the job created in step 7 and in is called “FILE_WATCHER_JOB” )

SQL Developer performs all the heavy listing when it comes to getting to this information.

From the SQL Developer Object Navigator select the Scheduler folder

Expand the folder and then the Jobs folder and you should see the job you created for the File Watcher, in this case it is called “FILE_WATCHER_JOB” (The job is created in Step 7)

Selecting the appropriate job will then bring up a list of tabs. Select the Run Log

From here you can see lots of useful information that should assist in your debugging.