How to convert local PL/SQL table to table I can select from

Question:

I am trying to figure out how I can convert an Oracle PL/SQL local table (of numbers) to a table that I can select from.

I want to pass this table into a cursor, and use it in the where clause, similar to this:

CURSOR my_csr
(in_table IN number_table)
IS
BEGIN
SELECT *
FROM table_x tx
WHERE tx.id_num IN (SELECT * FROM in_table)
END;

Currently, I have my program set up to loop through this local collection, and fetch the cursor with 1 number at a time. But I need it to take in the entire table of numbers, instead of just one. So that I can return all of my results at once.

Is there a way I can convert my local table type into a table that I can select from and use in queries?

Thanks!

Answer:

The short answer is "YES", you'll be pleased to hear. However the table type must be must be an attribute data type - in other words the type must be declared at the schema level rather than locally - which restricts this to VARRAY table types and NESTED TABLE types (i.e. you can't use Oracle PL/SQL associative arrays).

Let's look at an example. First of all we'll define the table type outside of our PL/SQL block:

create or replace type num_tab is table of number;
Next we'll declare an anonymous PL/SQL block to create and populate a local PL/SQL table of the type just created, declare a cursor to select from this table and then display the output.

We're going to cheat in this example and hard code the entries in the local table but we could populate the table from the Oracle database (by using BULK COLLECT for example). Here is our Pl/SQL code:


DECLARE

  local_table num_tab;

  CURSOR my_csr (in_table IN num_tab)
  IS
  SELECT *
  FROM employees
  WHERE department_id IN
    (SELECT column_value
    FROM TABLE(CAST (in_table AS num_tab))
    );

begin

  local_table := num_tab(10,20,30);
          -- populate PL/SQL table

  for emp in my_csr(local_table) loop
          -- run a cursor over it

     dbms_output.put_line('emp id = '||emp.employee_id);

  end loop;

end;
When we run this against the sample HR schema in the Oracle database it produces the following results:

emp id = 200
emp id = 202
emp id = 201
emp id = 119
emp id = 118
emp id = 117
emp id = 116
emp id = 115
emp id = 114

The important operation in the PL/SQL code is the CAST function combined with TABLE to create a table collection expression. This enables us to use the table in a SQL query as if it were a normal table stored in the Oracle database.

This example used a NESTED TABLE type, we could just as easily use a VARRAY. All we would need to do is change the type definition as follows:

create or replace type num_tab is varray(4) of number;
Obviously this puts a limit on the size of the table (in this case 4) which may not be what you want.

We could also populate the PL/SQL from the database by using the BULK COLLECT statement (instead of initializing the table with constant values) like this:

SELECT department_id BULK COLLECT INTO local_table FROM departments;