Using table type in SELECT statement

Question

I try to use variable of table type in SELECT statement, but it occurs error:

DECLARE
    TYPE v2t IS TABLE OF table_a.id%TYPE INDEX BY BINARY_INTEGER;
    TYPE test_type IS TABLE OF table_a.id%TYPE INDEX BY BINARY_INTEGER;
    test_table1 test_type;
    test_table2 test_type;
    databaseArray v2t;
BEGIN
    SELECT id BULK COLLECT INTO test_table1 FROM 
        (SELECT id FROM test_a WHERE some_flag = 1);
    FOR i IN 1..test_table1.COUNT LOOP
        databaseArray(i) := test_table1(i);
    END LOOP;

    SELECT id BULK COLLECT INTO test_table2 FROM 
           (SELECT id FROM TABLE(databaseArray));  --error ORA-22905
END;

test_table1 is not visible as table. Is any way to use test_table1 in SELECT statement? I need to have two subsets of results.

@DavidSilva - the type has to be declared at schema level, as the answer on the linked question shows, using `create type` - not just in the `declare` section. It has to be a database type, not a PL/SQL type, for you to refer to it in SQL (even SQL that is inside PL/SQL). At the moment `v2t` and `database_array` aren't adding anything. — Alex Poole, Apr 07 '14 at 14:13
@Alex Poole, is any way to do something similar without declaration anything at schema level ? — David Silva, Apr 07 '14 at 14:19

score 2 · Accepted Answer · answered Apr 07 '14 at 14:52

If you don't want to declare your own type at schema level, you could use a built-in table type, as long as there is one that matches your underlying data type and you don't have too many values to put in it:

DECLARE
  TYPE test_type IS TABLE OF user_objects.object_id%TYPE
    INDEX BY BINARY_INTEGER;
  test_table1 test_type;
  test_table2 test_type;
  databaseArray sys.odcinumberlist;
BEGIN
  SELECT object_id
  BULK COLLECT INTO test_table1
  FROM user_objects;

  databaseArray := sys.odcinumberlist();
  FOR i IN 1..test_table1.COUNT LOOP
    databaseArray.extend;
    databaseArray(i) := test_table1(i);
  END LOOP;

  SELECT column_value
  BULK COLLECT INTO test_table2
  FROM TABLE(databaseArray);
END;
/

anonymous block completed

Using table type in SELECT statement

1 Answers1