Lists as parameters (Version 8.0)
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 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
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 (
the ( select
cast(jpl_table_3.return_table(t_list) as jpl_array)
for r1 in c1(v_list) loop
r1.owner || ' - ' ||
r1.object_type || ' - ' ||
A demonstration of using the procedure
set serveroutput in size 10000
-- Create a variable of the array type
my_table jpl_array := jpl_array();
-- Insert some 'rows' into the type
my_table := jpl_array(
-- call the procedure, passing the one variable
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.