PL/SQL Use table variable as query table

Question

In PL/SQL is it possible to use a variable as a query a table?

Tried:

declare
   TYPE t_name IS TABLE OF varchar(50) INDEX BY PLS_INTEGER;
   v_names t_name;
begin
select name bulk collect into v_names from my_table;

select name from v_names where name = 'Max';
end;

Answer 1

Yes ... but not how you are doing it, for two reasons:

• Firstly, you do not have a collection (what you are calling a table variable) as you have used INDEX BY PLS_INTEGER so what you have is an associative array.
• Secondly, you can only use collections in SQL queries where the data type has been declared in the SQL scope (and yours is declared in PL/SQL).

So, first you need to create the type:

CREATE TYPE t_name IS TABLE OF VARCHAR2(50);

Then you can run the PL/SQL block:

DECLARE
  v_names t_name;
  v_name  VARCHAR2(50);
BEGIN
  SELECT name
  BULK COLLECT INTO v_names
  FROM my_table;

  SELECT COLUMN_VALUE
  INTO   v_name
  FROM   TABLE(v_names)
  WHERE  COLUMN_VALUE = 'Max';
  
  DBMS_OUTPUT.PUT_LINE( v_name );
END;
/

(Note: the table collection expression in the second query has the pseudo-column COLUMN_VALUE rather than any particular identifier from a table.)

db<>fiddle here