There is a question that appears on the internet newsgroup comp.databases.oracle.server fairly regularly about dealing with the problem of creating a list (of strings usually) in one piece of code, and then passing the list to another piece of code to be used in a SQL statement.
The first attempt to do this usually results in code fragments like:
var1 := "'A','B','C'" ... ... select * from tableX where colX in (:var1);
The idea being that by the time the SQL runs, that the database engine will receive the query:
select * from tableX where colX in ('A','B','C');
and return rows which satisfy the three different conditions.
Unfortunately, if you pass one bind variable, the thing that arrives is a single bind variable, and cannot be broken up into an arbitrary number of separate values. In this the database responds by searching for all rows where colX is an exact match to the string '''A''',''B'',''C'''. (where the doubled up single-quotes would be used to pass the value in a somple SQL*Plus session)
Oracle 8.0 with the object option, however, introduces the possibility of passing a single variable to a procedure, or cursor, but having the code break the variable into a number of distinct values. My earlier article on PL/SQL tables as Cursors is the starting point for this process, and you should read that article before you continue with this one.
The sample code uses my package jpl_table_3, and the jpl_array described in that article, and it is a parameter of type jpl_array that can become the multiple set of elements in our IN-list.
A Sample procedure
rem rem Script: in_list.sql rem Author: Jonathan Lewis rem Dated: June 99 rem Oracle: 8.0.4 (NT) rem Purpose: Demo passing a list of values as a parameter rem Uses package jpl_table_3 for table of values rem
create or replace procedure in_list_demo (v_list in jpl_array) as
-- A procedure taking one IN parameter -- and a cursor taking one IN parameter -- Note how the 'the(select cast() from dual) converts one parameter into -- a list of values (actually a subquery, but that's often close enough) cursor c1 (t_list in jpl_array) is select owner, object_type, object_name from all_objects t1 where object_name in ( select description from the ( select cast(jpl_table_3.return_table(t_list) as jpl_array) from dual ) t2 ) ;
begin for r1 in c1(v_list) loop dbms_output.put_line( r1.owner || ' - ' || r1.object_type || ' - ' || r1.object_name ); end loop; end; . /
A demonstration of using the procedure
set serveroutput in size 10000 declare -- Create a variable of the array type my_table jpl_array := jpl_array(); begin
-- Insert some 'rows' into the type my_table := jpl_array( jpl_row(null,'PLAN_TABLE'), jpl_row(null,'ALL_TABLES'), jpl_row(null,'DUAL') );
-- call the procedure, passing the one variable in_list_demo(my_table);
end; . /
The results
PUBLIC - SYNONYM - ALL_TABLES SYS - VIEW - ALL_TABLES SYS - TABLE - DUAL PUBLIC - SYNONYM - DUAL PUBLIC - SYNONYM - PLAN_TABLE SYSTEM - TABLE - PLAN_TABLE
PL/SQL procedure successfully completed.
Interestingly tkprof80 was unable to handle parsing the statement executed by the procedure, but the execution path dumped into the trace file itself showed an example of the new 'collection iterator' operation.
STAT #5 id=9 cnt=3 pid=8 pos=1 obj=0 op='VIEW ' STAT #5 id=10 cnt=3 pid=9 pos=1 obj=0 op='SORT UNIQUE ' STAT #5 id=11 cnt=3 pid=10 pos=1 obj=0 op='COLLECTION ITERATOR '
Apart from this, the main difference in execution paths between the procedure's path and the path taken by a simple SQL staement using 3 bind variables was that the simple SQL used a simple filter operation with the three values, whereas the procedural version with the collection type iterator used a sort/merge join between the 'view' created from the collection and the results generated by the rest of the query. This could make a big difference to your performance in real life - so test thoroughly with real data volumes before using this technique.