ORACLE PL / SQL: Dynamic SQL Selection Using a Collection

Question

Is it possible to create a dynamic SQL statement that fetches from an existing collection?

l_collection := pack.get_items(
                i_code => get_items_list.i_code ,
                i_name => get_items_list.i_name );

Now let's say I want to select COUNT from this collection using dynamic SQL. Is it possible? In addition, I want to make an additional choice from this collection.

Answer 1

If a collection type is declared at the schema level, it can be used in SQL operations, including dynamic ones. You need to explicitly cast it to the appropriate collection type, or the SQL engine does not know what type it is.

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM TABLE(CAST(:collection AS collection_type))'
   INTO l_count
   USING l_collection
  ;

, , SQL, , . , , , . SQL :

SELECT COUNT(*) INTO l_count FROM TABLE(CAST(l_collection AS collection_type));

, , , SQL, l_count := l_collection.COUNT.

-

CREATE OR REPLACE TYPE testtype AS OBJECT( x NUMBER, y NUMBER);
/

CREATE OR REPLACE TYPE testtypetab AS TABLE OF testtype;
/

DECLARE
  t  testtypetab := testtypetab();
  l_count integer;
BEGIN
  -- Populate the collection with some data
  SELECT testtype(LEVEL, LEVEL) BULK COLLECT INTO t FROM dual CONNECT BY LEVEL<21;

  -- Show that we can query it using inline SQL
  SELECT count(*) INTO l_count FROM TABLE(CAST(t AS testtypetab));
  dbms_output.put_line( l_count );

  -- Clear the collection
  t.DELETE;

  -- Show that we can query it using dynamic SQL
  EXECUTE IMMEDIATE 'select count(*) from table(cast(:collection as testtypetab))'
    into l_count using t;
  dbms_output.put_line( l_count );
END;
/