JL Computer Consultancy

Lists as parameters (Version 8.0)

June 1999


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.

Back to Main Index of Topics