Executing a query using a Collection as argument in Oracle

Question

Is there any way I can execute immediate a query with a collection as its argument.

I want to define a type as type my_type as table of number and then execute a dynamic query (created by concating proper parts) using execute immediate QUERY using COLLECTION.

When I write such a code I get PLS-00457 expressions has to be of SQL types.

If you [edit] your question and post the code you wrote, I may be able to tell you why you are getting Oracle error `PLS-00457`. — Abra, May 09 '20 at 10:48
What version of Oracle are you using? Oracle 12c introduced better compatibility between SQL and PL/SQL package types. In 11g, you might have to define the types as SQL objects to make this work. — Jon Heller, May 10 '20 at 01:43

score 0 · Answer 1 · answered May 09 '20 at 12:19

From oracle documentation :https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/11_dynam.htm Example 7-4 Dynamic SQL for Object Types and Collections

The below example illustrates the use of objects and collections. Suppose you define object type Person and VARRAY type Hobbies, as follows:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

Using dynamic SQL, you can write a package that uses these types:

CREATE OR REPLACE PACKAGE teams AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
         ' (pers Person, hobbs Hobbies)';
   END;

   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p Person,
      h Hobbies) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;

   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE RefCurTyp IS REF CURSOR;
      cv RefCurTyp;
      p  Person;
      h  Hobbies;
   BEGIN
      OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH cv INTO p, h;
         EXIT WHEN cv%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
      END LOOP;
      CLOSE cv;
   END;
END;
/

From an anonymous block, you might call the procedures in package TEAMS:

DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   teams.create_table(team_name);
   teams.insert_row(team_name, Person('John', 31),
      Hobbies('skiing', 'coin collecting', 'tennis'));
   teams.insert_row(team_name, Person('Mary', 28),
      Hobbies('golf', 'quilting', 'rock climbing'));
   teams.print_table(team_name);
END;
/

score 0 · Answer 2 · answered May 10 '20 at 04:14

Yes, you can do that but 1) type should be defined as OBJECT 2) Oracle version should be 11g or higher 3) if your dynamic block has plsql code, you can assign this collection value inside dynamic block variable and then use it 4) if your dynamic block is just some sql, you need to cast the collection to table first using Table(collection) keyword

Executing a query using a Collection as argument in Oracle

2 Answers2