SQL and Collections


One of the most common reasons people appear to be using dynamic SQL is to handle the case when the user can specify one, two or any amount of values to be searched on. For example, the user may select (say) two names from a LOV on people, and so the where-clause part of the query would look like:

where SURNAME in ( 'SMITH', 'WILLIAMS')

but of course if they had selected FOUR names from the LOV, then the where-clause part of the query would look like:

where SURNAME in ( 'SMITH', 'WILLIAMS','DIO','MALMSTEEN')

So throughout forms applications, the way people tend to handle this is with dynamic piece of SQL along the lines of: l_sql := 'select * from persons where surname in ('|| lv_list_of_names ||')';

which we then process with EXEC_SQL or similar. As we all now know, its a bad thing to do on performance grounds (no bind variables = no performance). But its also a pain to code correctly - we have to carefully build up the list of names into a comma separated list, we have to check for surnames that contains quotes, etc etc. And its a big security hole as well

You might be thinking that surely it can't be possible to process an arbritrary number of in-list items with the same single SQL ? Well, yes it can. All it takes a little knowledge of the object-relational options within Oracle.

Firstly, we created a nested table type (you'd only need to do this once for the whole database) to hold a list of strings that have been (say) picked from a LOV

SQL> create or replace type varchar2_list as table of varchar2 (255);
  2  /

Type created.

Then we can create a simple function which takes a comma-separated list of predicate values and convert them to the varchar2_list type.

SQL> create or replace
  2  function in_list( p_string in varchar2 ) return varchar2_list as
  3    l_string        long default p_string || ',';
  4    l_data          varchar2_list := varchar2_list();
  5    n               number;
  6  begin
  7    loop
  8      exit when l_string is null;
  9      n := instr( l_string, ',' );
 10      l_data.extend;
 11      l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 )) );
 12      l_string := substr( l_string, n+1 );
 13    end loop;
 14    return l_data;
 15  end;
 16  /

Function created.

And here is where things get very sweet...Oracle can re-cast the results of such a function into a relational structure on the fly. For example, we can take the string 'SMITH,JONES,BROWN' and automatically treat it as if they were rows in a table

SQL> select *
  2  from   table(cast(in_list('SMITH,JONES,BROWN') as varchar2_list));

COLUMN_VALUE
--------------------------
SMITH
JONES
BROWN


"Big deal" I hear you say. But once you can convert a string to a table, then you can of course, use it within normal SQL. For example, lets build a string of people's surnames (in the variable "list_of_people"). We can then query the NOMINAL table for just those people using static SQL

SQL> declare
  2    list_of_people varchar2(100) := 'SMITH,WILLIAMS,DIO,MALMSTEEN';
  3  begin
  4    for i in ( select id, date_created
  5               from   person
  6               where  upper(surname) in (
  7                 select *
  8                 from   table(cast(in_list(list_of_people) as varchar2_list))) ) loop
  9      dbms_output.put_line('ID           = '||i.id);
 10      dbms_output.put_line('DATE_CREATED = '||i.date_created);
 11    end loop;
 12  end;
 13  /
ID           = 9000001
DATE_CREATED = 01/OCT/03
ID           = 9000002
DATE_CREATED = 01/OCT/03
ID           = 9000003
DATE_CREATED = 01/OCT/03
ID           = 9000004
DATE_CREATED = 01/OCT/03
ID           = 9000010
DATE_CREATED = 01/OCT/03
ID           = 9000023
DATE_CREATED = 02/OCT/03
ID           = 9000026
DATE_CREATED = 02/OCT/03

PL/SQL procedure successfully completed.

Notice that the SQL in red above does not contain any literals - it doesn't matter what the list of people being sought is, we can re-use the same SQL over and over. No dynamic SQL needed, no literal strings needed.

In fact, anything that you can convert into the nested table type varchar2_list, can be used. So (for example), its trivial to change our demo so that rather than passing a string list of names, you could pass any SQL statement to generate the list of surnames that we are interested in. All we need do it alter our IN_LIST function to loop through a ref cursor as opposed to a string.

SQL> create or replace
  2  function in_list( p_string in varchar2 ) return varchar2_list as
  3    type rc is ref cursor;
  4    l_cursor     rc;
  5    l_tmp        long;
  6    l_data       varchar2_list := varchar2_list();
  7  begin
  8    open l_cursor for p_string;
  9    loop
 10        fetch l_cursor into l_tmp;
 11        exit when l_cursor%notfound;
 12        l_data.extend;
 13        l_data(l_data.count) := l_tmp;
 14    end loop;
 15    close l_cursor;
 16    return l_data;
 17  end;
 18  /

Function created.

Now my "list_of_people" variable can be a SQL statement, and my loop still works just fine.

SQL> declare
  2    list_of_people varchar2(100) := 'select surname from nominal '||
  3                                    ' where surname like ''S%''';
  4  begin
  5    for i in ( select id, date_created
  6               from   person
  7               where  upper(surname) in (
  8                 select *
  9                 from   table(cast(in_list(list_of_people) as varchar2_list))) ) loop
 10      dbms_output.put_line('ID           = '||i.id);
 11      dbms_output.put_line('DATE_CREATED = '||i.date_created);
 12    end loop;
 13  end;
 14  /
ID           = 9000001
DATE_CREATED = 01/OCT/03
ID           = 9000010
DATE_CREATED = 01/OCT/03
ID           = 9000021
DATE_CREATED = 02/OCT/03
ID           = 9000022
DATE_CREATED = 02/OCT/03
ID           = 9000023
DATE_CREATED = 02/OCT/03
ID           = 9000024
DATE_CREATED = 02/OCT/03
ID           = 9000025
DATE_CREATED = 02/OCT/03
ID           = 9000026
DATE_CREATED = 02/OCT/03
ID           = 9000027
DATE_CREATED = 02/OCT/03
ID           = 9000074
DATE_CREATED = 07/OCT/03

PL/SQL procedure successfully completed.

There are a myriad of uses for collections and object types in PL/SQL. You'll find all the details on using object types within PL/SQL within the PL/SQL reference and the Application Developer - Object Relational reference.