Using table type in SELECT statement [duplicate]

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
– Alex Poole, Commented Apr 7, 2014 at 14:13
@Alex Poole, is any way to do something similar without declaration anything at schema level ? — David Silva
– David Silva, Commented Apr 7, 2014 at 14:19

Alex Poole · Accepted Answer · 2014-04-07 14:52:36Z

2

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

answered Apr 7, 2014 at 14:52

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using table type in SELECT statement [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related