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.

Solution

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

Comments

[jlrolin]

Hmm... doesn't seem to work. Keeps giving me an expression is of wrong type error.

[Dave Costa]

I added a more complete example that runs successfully for me. It seems like "expression is of wrong type" usually means that you are trying to assign an object to a non-object variable or vice versa. If you still have trouble, perhaps you should post a small code sample that shows your specific problem.

[jlrolin]

Yeah, something is wonky with my SQL Developer install. I'm getting this error even if I remove the code.

[jlrolin]

I simply ran it without debugging and it is pumping out data. Looks like it works, thanks a bunch.

[Gary Myers]

SQL Developer has problems if it can't easily map datatypes. If you add an appropriate CAST(....) it often gets rid of the "Expression is of wrong type" error