Can a table variable be used in a select clause, where is the condition?

Question

I have a stored procedure that executes a two-step request. The first step is to collect a list of characters of type VARCHAR2 from the table and assemble them into a table variable defined as follows:

TYPE t_cids IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; v_cids t_cids; 

So basically I:

 SELECT item BULK COLLECT INTO v_cids FROM table_one; 

This works until the next bit.

Now I want to use this collection in the where clause of another request in the same procedure, for example:

 SELECT * FROM table_two WHERE cid IN v_cids; 

Is there any way to do this? I can select a single item, but I would like to use a table variable, since a will use a regular table. I tried the options using nested selections, but that doesn't work either.

Thank you very much,

Zach

Answer 1

You have several options for how you achieve this.

If you want to use a collection, you can use the TABLE function to select it, but the type of collection you are using becomes important.

for a brief example, this creates a database type, which is a table of numbers:

 CREATE TYPE number_tab AS TABLE OF NUMBER / 

Type created.

The next block then fills the collection and performs an elementary selection from it, using it as a table and attaching it to EMP tables (with some output, so that you can see what is happening):

 DECLARE -- Create a variable and initialise it v_num_tab number_tab := number_tab(); -- -- This is a collection for showing the output TYPE v_emp_tabtype IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER; v_emp_tab v_emp_tabtype; BEGIN -- Populate the number_tab collection v_num_tab.extend(2); v_num_tab(1) := 7788; v_num_tab(2) := 7902; -- -- Show output to prove it is populated FOR i IN 1 .. v_num_tab.COUNT LOOP dbms_output.put_line(v_num_tab(i)); END LOOP; -- -- Perform a select using the collection as a table SELECT e.* BULK COLLECT INTO v_emp_tab FROM emp e INNER JOIN TABLE(v_num_tab) nt ON (e.empno = nt.column_value); -- -- Display the select output FOR i IN 1 .. v_emp_tab.COUNT LOOP dbms_output.put_line(v_emp_tab(i).empno||' is a '||v_emp_tab(i).job); END LOOP; END; 

From this you can see that the TYPE collection of the database (number_tab) was considered as a table and could be used as such.

Another option is to simply attach your two tables, which you select in your example:

 SELECT tt.* FROM table_two tt INNER JOIN table_one to ON (to.item = tt.cid); 

There are other ways to do this, but the former can best suit your needs.

Hope this helps.

Answer 2

 --Doesn't work. --SELECT item BULK COLLECT AS 'mySelectedItems' INTO v_cids FROM table_one; SELECT table_two.* FROM table_two INNER JOIN v_cids ON table_two.paramname = v_cids.mySelectedItems; 

If I do not understand the question, this should only return results that are in the table variable.

Note. I have never used Oracle, but I assume this case will be the same.