ORACLE PL/SQL: Dynamic SQL Select using a Collection

Question

Is it possible to create a dynamic SQL statement that pulls 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 a COUNT from that collection using dynamic SQL. Is that possible? Furthermore, I want to do a sub select from that collection as well.

Answer 1

If the collection type is declared at the schema level, it can be used in SQL statements, including dynamic ones. You need to explicitly cast it to the proper collection type, or the SQL engine has no idea what type it is.

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

I'm not sure if there's some other reason you want to use dynamic SQL, or if you're just assuming that it's necessary in this case. It shouldn't be necessary if all you want to do is select the count. This inline SQL should work fine:

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

Of course, if that's all you want you don't need SQL at all, just l_count := l_collection.COUNT.

Edit -- adding fully worked out example

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;
/