How to use weak ref cursors and bulk collects into a table of objects to clean up data

The problem I was facing was simple. You have a table with data. The data in that table should be validated. Invalid rows should be deleted. For each deleted row an entry in a logfile should be created. The validation of the rows could be performed by expensive queries. The simple solution would like:

  for r_rec in c_badrows
  loop
    .... write data to the logfile
  end loop

  delete from mytable where ...

This way the query to lookup the bad rows has to be performed twice. 
That is now what I need in the heavy batch I am currently implementing. The other problem is that I have several queries that determine the bad rows. The solution to the probem was as follows:

• Use ref cursors for the different bad row queries. This way the queries could be arguments to the clean-up procedure
• use bulk fetches to query from the ref cursor. Loop through the result and log the data into the logfile
• Use the table cast operators to delete the bad rows.

The trick is to use weak ref cursors in stead of strong ref cursors (I looked this up with my colleague Alex). Strong ref cursors should be based on record types. But I needed to fetch into a table of objects to perform the bulk delete at once. You can do this with weak ref cursors as the sample code will show you.

First I created two new types. One object and one table of object based on the first type:

SQL> create type myobject as object (id number, name varchar2(10));

Type created.

SQL> create type my_object_table as table of myobject;

Type created.

The actual procedure could look like the following. It processes a 1000 rows at a time. I tested this also for a value of 100. This made the procedure several times slower. This is understandable since the number of context switches from PL/SQL to SQL is 10 times more.

create or replace procedure my_clean_up( p_fp   in utl_file.file_type
                                       , p_data in sys_refcursor)
is
  r_data my_object_table;
begin
  loop
    r_data := my_object_table(); -- Initialise the object
    fetch p_data bulk collect into r_data limit 1000;    -- Fetch 1000 rows at a time

    for l_index in 1..r_data.count
    loop
      utl_file.put_line(p_fp, r_data(l_index).name|| ' bad row is deleted from the table');
    end loop;

    delete from my_table
    where id in (select id
                 from table(cast(r_data as my_object_table)) -- cast the object to a table, so it can be used from SQL
                 );

    exit when p_data%notfound;
  end loop;

  close p_data;
end;

The trick is to use weak ref cursors, as I noted earlier. This way a refcursor returning myobject rows could be provided as the input parameter for the procedure. A function returning a ref cursor like this could look like this:

function get_bad_names
return sys_refcursor
is
  c_data sys_refcursor;
begin
  open c_data
  for
    select myobject(id, name) -- !! Call the objects constructor
    from my_table
    where .......

   return c_data;
end;

The last part of course is to use all this. The following final code example shows how. It cleans up the table and writes logdata to a file

procedure test
is
  l_fp         utl_file.file_type;
begin
  l_fp := utl_file.fopen('c:\temp', 'mylogfile','w');
  if not utl_file.is_open(l_fp)
  then
    -- handle the error
  end if;

  my_clean_up(l_fp, get_bad_names); -- This is the one doing it all

  fclose(l_fp);
end;