How to return a collection from dynamic SQL having RETURNING clause

Question

I have a PL/SQL block like this:

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

DECLARE

    updatedEmp NUMBER_TABLE_TYPE;
    sqlstr VARCHAR2(1000);
    tableName VARCHAR2(30) := 'EMPLOYEE';
    deptId NUMBER := 12;

BEGIN

    sqlstr := 'UPDATE '||tableName||' SET SALARY = SALARY * 2 WHERE DEPT_ID = :deptId '||
              'RETURNING EMP_ID BULK COLLECT INTO :res'; 
    EXECUTE IMMEDIATE sqlstr BULK COLLECT INTO updatedEmp USING deptId;

END;

RETURNING BULK COLLECT INTO clause would be simple for static DML, however in my application it is a dynamic one. I tried several ways but non of them worked out. Any idea how to achieve this?

Answer 1

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

DECLARE

    updatedEmp NUMBER_TABLE_TYPE;
    sqlstr VARCHAR2(1000);
    tableName VARCHAR2(30) := 'EMPLOYEE';
    deptId NUMBER := 12;

BEGIN

    sqlstr := 'UPDATE '||tableName||' SET SALARY = SALARY * 2 WHERE DEPT_ID = :deptId '||
              'RETURNING EMP_ID  INTO :res'; 

EXECUTE IMMEDIATE sqlstr 
      USING deptId RETURNING BULK COLLECT INTO updatedEmp ;

END;