Can a table variable be used in a select statement where clause?

Question

I have a stored procedure that is doing a two-step query. The first step is to gather a list of VARCHAR2 type characters from a table and collect them into a table variable, defined like this:

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

So basically I have:

SELECT item BULK COLLECT INTO v_cids FROM table_one;

This works fine up until the next bit.

Now I want to use that collection in the where clause of another query within the same procedure, like so:

SELECT * FROM table_two WHERE cid IN v_cids;

Is there a way to do this? I am able to select an individual element, but I would like to use the table variable like a would use a regular table. I've tried variations using nested selects, but that doesn't seem to work either.

Thanks a lot,

Zach

score 9 · Accepted Answer · answered Dec 02 '11 at 10:39

You have several choices as to how you achieve this.

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

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

CREATE TYPE number_tab AS TABLE OF NUMBER
/

Type created.

The next block then populates the collection and performs a rudimentary select from it using it as a table and joining it to the EMP table (with some output so you can see what's 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;

You can see from this that the database TYPE collection (number_tab) was treated as a table and could be used as such.

Another option would be to simply join your two tables you are selecting from in your example:

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

There are other ways of doing this but the first might suit your needs best.

Hope this helps.

Ahh. This works perfectly. I was casting the variable as my type, and then joining it, and was getting an error. Even better is the option of join the tables directly...I was doing a lot of dynamic SQL and got caught up in the flow of the thing and missed the obvious completely. Thanks a lot! — zmouser, Dec 02 '11 at 16:28

Toast · Answer 2 · 2011-12-01T21:24:11.403

-1

--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;

Unless I'm misunderstanding the question, this should only return results that are in the table variable.

Note: I've never used Oracle, but I imagine this case would be the same.

edited Dec 01 '11 at 21:24

answered Dec 01 '11 at 20:52

Toast

424
2
9

Thanks for the answer. How do I know what the parameter is named in a table variable? Does it default to the selected column name? – zmouser Dec 01 '11 at 21:02
I believe it defaults as you said. I'm not 100% sure on this. Even if it doesn't, you can use `AS 'columnname'` in your original select statement to explicitly call it something. - I edited this into the answer. – Toast Dec 01 '11 at 21:04
I tried the explicit cast in the select statement, which produced the same error ('cannot access rows from a non-nested table'), and then tried to add the `AS`, as you did, but that results in a syntax error. – zmouser Dec 01 '11 at 21:19
Hmm.. sorry about that. I would just run the query and return the temp table. From there you should be able to see the default column name, no? – Toast Dec 01 '11 at 21:22
Yeah. Perhaps I will just use a temporary table and not a table variable. I cannot actually do a select on the variable and see the column name (that I know of) because I would have to loop through it, usually using the index to access the data, which is mostly how I've used these. – zmouser Dec 01 '11 at 21:28

Can a table variable be used in a select statement where clause?

2 Answers2

Linked