Execute Immediate Returning Bulk Collect - Dynamic DML Collection

One of our team member wanted to execute a dynamic DML using EXECUTE IMMEDIATE and return updated rows into a PL/SQL Collection. For the purpose of demonstration and ease I have rewritten the examples on EMP table. The code written by the team member was as follows -

SQL> DECLARE
  2     TYPE emp_record IS RECORD
  3     (
  4        EMPNO NUMBER(4),
  5        ENAME VARCHAR2(10),
  6        HIREDATE DATE,
  7        SAL NUMBER(7,2),
  8        DEPTNO NUMBER(2)
  9     );
 10     TYPE l_emp IS TABLE OF emp_record;
 11     bonus_amt number := 100;
 12     sql_stmt  VARCHAR(200);
 13  BEGIN
 14     sql_stmt := 'UPDATE emp SET sal = sal + :1
 15               RETURNING EMPNO, ENAME, HIREDATE, SAL, DEPTNO
 16        INTO :2, :3, :4, :5, :6';
 17     EXECUTE IMMEDIATE sql_stmt
 18        USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
 19  END;
 20  /
      USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
                                                  *
ERROR at line 18:
ORA-06550: line 18, column 51:
PLS-00321: expression 'L_EMP' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 17, column 4:
PL/SQL: Statement ignored

BULK COLLECT and RETURNING clause with EXECUTE IMMEDIATE could be little tricky to handle and if not done correctly. In my experience I have seen people doing some common mistakes which may lead to following ORA EXCEPTION and PLS Errors.
1. PLS-00321: expression 'L_EMP' is inappropriate as the left hand side of an assignment statement
2. ORA-01008: not all variables bound
3. PLS-00429: unsupported feature with RETURNING clause

PLS-00321 signifies that the variable or collection in which we are returning the values is not actually a variable or collection but the TYPE itself.
ORA-01008 might occur when we do not have appropriate number of bind variables in INTO or USING clause.
PLS-00429 with returning clause with bulk collect usually occurs when the RECORD type is not defined at SQL Engine level but at PL/SQL level itself

The solution to the above code is as following, In which I have done 3 changes
- Created TYPE emp_t AS OBJECT
- Collection type is created in PL/SQL and variable is defined for collection type
- Type Cast the multiple values into Object type in returning clause

SQL> create type emp_t as object
  2  (
  3   EMPNO                                     NUMBER(4),
  4   ENAME                                              VARCHAR2(10),
  5   HIREDATE                                           DATE,
  6   SAL                                                NUMBER(7,2),
  7   DEPTNO                                             NUMBER(2)
  8  );
  9  /

Type created.

SQL> DECLARE
  2     type emp_tt is table of emp_t index by binary_integer;
  3     l_emp emp_tt;
  4     bonus_amt number := 100;
  5     sql_stmt  VARCHAR(200);
  6  BEGIN
  7     sql_stmt := 'UPDATE emp SET sal = sal + :1
  8                  RETURNING emp_t(EMPNO, ENAME, HIREDATE, SAL, DEPTNO)
  9   INTO :2';
 10     EXECUTE IMMEDIATE sql_stmt
 11        USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
 12  END;
 13  /

PL/SQL procedure successfully completed.

I hope I was able to explain the key points of Dynamic DML returning collection using BULK COLLECT and EXECUTE IMMEDIATE. I also hope that you have enjoyed reading this blog post. Please check out following related post too

Related Posts:
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- PLSQL Tuning: Bind Variables and execute immediate
- Bulk Collect with Limit Clause and %NOTFOUND
- DBMS_PROFILER: How to analyze pl/sql performance
- Reduce database calls by posting Multiple Records from Application to Database
- Avoiding unnecessary function calls to optimize SQL statements
- Passing Parameter to CURSOR in Oracle
- Two Dimension Array in PL/SQL