RETURNING Clause

The RETURNING clause to INSERTs, UPDATEs, and DELETEs helps to retrieve data from the rows affected by the DML statement. If the DML operation is affecting only one row then static SQL can be used.

SQL> var empid number

SQL> delete employees where first_name='Steven' returning employee_id into :empid;

1 row deleted.

SQL> print empid
       100

SQL> 

There are cases where the primary key value is being updated by a trigger. So the insert statement may not contain the primary key value.

SQL> var empid number

SQL> insert into employees(first_name, last_name,HIRE_DATE,JOB_ID) values('Steven', 'King', to_date('110697','mmddyy'), 'Programmer') returning employee_id into :empid;

1 row created.

SQL> print empid
       129

SQL> 

If the DML affects more than one row then the bind variable cannot hold an array of values.

SQL> select count(*) from employees;
         20

SQL> 
SQL> delete employees returning employee_id into :empid;
delete employees returning employee_id into :empid
*
ERROR at line 1:
ORA-24369: required callbacks not registered for one or more  bind handles

SQL> select count(*) from employees;
         0

SQL> select count(*) from employees;
         20

SQL> 
SQL> 
SQL> declare
  2  v_empid number;
  3  BEGIN
  4  delete employees returning employee_id into v_empid;
  5  END;
  6  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4


SQL> 
SQL> select count(*) from employees;
         20

The following PL/SQL code can be used to do bulk update dynamically and also return the values to a collection.

declare
TYPE numtab is table of number;
type csvtab is table of varchar2(100);
empno numtab;
ename csvtab;
e2 numtab;
n1 number;
BEGIN
select EMPLOYEE_ID, FIRST_NAME bulk collect INTO empno, ename from employees;
forall n1 in empno.first..empno.last
execute immediate
'update employees set first_name=:id where employee_id=:id2 returning employee_id into :id3' using ename(n1), empno(n1) RETURNING BULK COLLECT INTO e2;
END;
/

The following statement returns column sal from deleted rows and stores the column values in the elements of a host array:
You can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk-binds column values incrementally.
In the following example, if collection depts has 3 elements, each of which causes 5 rows to be deleted, then collection enums has 15 elements when the statement completes:

declare
j number;
v_empid NUMTAB := NUMTAB();
v_depts NUMTAB := NUMTAB();
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Dynamic Delete Processing: ');
v_depts.extend();
v_depts(1) := '100';
FORALL j IN v_depts.FIRST..v_depts.LAST
DELETE FROM employees WHERE DEPARTMENT_ID = v_depts(j)
RETURNING employee_id BULK COLLECT INTO v_empid;
IF v_empid.count>0 THEN
for j in v_empid.FIRST..v_empid.LAST LOOP
dbms_output.put_line(v_empid(j));
END LOOP;
END IF;
END;
/

PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing. To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement. All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:

• %BULK_EXCEPTIONS(i).ERROR_INDEX holds the "iteration" of the FORALL statement during which the exception was raised.
• %BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.

For the employees table enforce a CHECK constraint. The update statement where the salary exceeds 10000 will fail and will be handled by the exception.

SQL> alter table employees modify(salary check(SALARY<10000));

Table altered.



declare
j number;
v_empid NUMTAB := NUMTAB();
v_depts NUMTAB := NUMTAB();
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Dynamic Delete Processing: ');
select distinct department_id BULK COLLECT INTO v_depts from employees;
FORALL j IN v_depts.FIRST..v_depts.LAST SAVE EXCEPTIONS
update employees set salary=salary+1000 WHERE DEPARTMENT_ID = v_depts(j)
RETURNING employee_id BULK COLLECT INTO v_empid;
IF v_empid.count>0 THEN
for j in v_empid.FIRST..v_empid.LAST LOOP
dbms_output.put_line(v_empid(j));
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('Number of UPDATE statements that failed: ' || SQL%BULK_EXCEPTIONS.COUNT);
   FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      dbms_output.put_line('Error #' || i || ' occurred during '||
         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      dbms_output.put_line('Error message is ' ||
         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/