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;