Using local table type in SELECT statement without specified index

Question

Is there any way to use a local table type into IN clause at SELECT statement without specifing index of table type?

DECLARE
    TYPE temp_rec_TYPE IS RECORD (
        n_temp_id NUMBER
    );

    TYPE temp_tab_TYPE IS TABLE OF temp_rec_TYPE INDEX BY PLS_INTEGER;
    temp_tab temp_tab_TYPE;

    n_temp_id2 NUMBER;
BEGIN
    temp_tab(1).n_temp_id := 1;

    FOR temp_rec IN (SELECT * FROM example_table WHERE id IN temp_tab)
    LOOP
        NULL;
    END LOOP;
END;

Executed return 'PLS-00382: expression is of wrong type'

This was asked before, I think: http://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement — vercelli, Jul 12 '16 at 10:26

score 0 · Accepted Answer · answered Jul 12 '16 at 10:40

No, SQL expressions can only reference types generated in the SQL scope (not the PL/SQL scope - i.e. not in a package nor a PL/SQL block).

What you want is something like:

CREATE TYPE number_list IS TABLE OF NUMBER;
/

DECLARE
  temp_tab number_list;
BEGIN
  temp_tab := number_list( 1 );

  FOR temp_rec IN (SELECT * FROM example_table WHERE id MEMBER OF temp_tab)
  LOOP
    NULL;
  END LOOP;
END;
/

score 0 · Answer 2 · answered Jul 12 '16 at 21:47

You can do it in Oracle 12c, as long as the PL/SQL type is defined in a package specification. Here is an example:

CREATE OR REPLACE PACKAGE matt_p1 AS
  TYPE temp_rec_type IS RECORD (n_temp_id NUMBER);
  TYPE temp_tab_type IS TABLE OF temp_rec_type
    INDEX BY PLS_INTEGER;

END matt_p1;
/

DECLARE

  temp_tab     matt_p1.temp_tab_type;

  n_temp_id2   NUMBER;
BEGIN
  SELECT object_id
  BULK COLLECT INTO temp_tab
  FROM  dba_objects 
  WHERE OBJECT_NAME LIKE 'DBA_TAB%';

  FOR temp_rec IN (SELECT object_name
                   FROM   dba_objects
                   WHERE  object_id IN ( SELECT n_temp_id FROM TABLE(temp_tab))) LOOP
    dbms_output.put_line(temp_rec.object_name);
  END LOOP;
END;

Using local table type in SELECT statement without specified index

2 Answers2