techblog 

You may have worked with collections in SQL and PL/SQL already. Collections are basically arrays holding columns or records of a user-defined data type. They come in various flavors. They can be defined as PL/SQL variables, but also as data types within the database. For example like this:

create type test_coll_type is table of number;
/

Imagine you have a procedure receiving such an array filled with id’s as an IN parameter. Within the procedure we need to check whether any of the values in this array exist within a table in the database. One’s first idea to solve this would probably be something like this:

create table x(id number, name varchar2(30));

ID NAME
---------- ------------------------------
1 James
2 Mark
3 John

create or replace procedure who_walks_the_dog1(p_coll   test_coll_type)
is
   l_name varchar2(30);
begin
   for i in p_coll.first..p_coll.last
   loop
     if p_coll(i) is not null
     then
      begin
      select name into l_name from x where id = p_coll(i);
      exception
      when no_data_found then null;
      end;

      dbms_output.put_line(l_name||' walks the dog');
     end if;
   end loop;
end who_walks_the_dog1;
/

Of course that is not very pretty code and it took me longer to write than I initially thought it would take. It also means a lot of calls to the database. That creates a lot of switches between the SQL and PL/SQL engine causing performance degradation.

It would be ideal if we could just take the list of ID’s and use it in a WHERE clause saying “WHERE id in (list of id’s)”.

Here is how you do it. You use the TABLE operator that basically converts a collection to a normal table so you can use the collection in SQL statements. That looks like this:

create or replace procedure who_walks_the_dog2(p_coll   test_coll_type)
is
type name_tabtyp is table of varchar2(30) index by binary_integer;
name_tab name_tabtyp;
begin
select name bulk collect into name_tab from x where id in (select column_value from table(p_coll));

if name_tab.count > 0
then
for i in name_tab.first..name_tab.last
loop
dbms_output.put_line(name_tab(i)||' walks the dog');
end loop;
end if;
end who_walks_the_dog2;
/

The TABLE operator allows you to write shorter, simpler code that’s more intuitive to read and it should be faster especially when you process a lot of rows. Test it like this.

declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(5);
   test_coll(1) := 1;
   test_coll(2) := 2;

   who_walks_the_dog2(test_coll);
end;
/

Collections and the TABLE operator have lots more interesting applications. Perhaps more in the next post.