Support multi-row UPDATE and DELETE RETURNING for Oracle using PL/SQL RETURNING .. BULK COLLECT INTO

[lukaseder]

In addition to single RETURNING clauses, it would be also very useful to support UPDATE and DELETE .. RETURNING for multi-row DML statements. This is possible with PL/SQL

UPDATE t SET x = 1 WHERE 1 = 1 RETURNING y BULK COLLECT INTO ?;

Or more specifically:

declare
  t0 dbms_sql.number_table;
  t1 dbms_sql.date_table;
  c0 sys_refcursor;
  c1 sys_refcursor;
begin
  update "TEST"."T_2155"
  set "TEST"."T_2155"."D1" = date '2003-03-03'
  returning 
    "TEST"."T_2155"."ID", 
    "TEST"."T_2155"."D1"
  bulk collect into t0, t1;
  ? := sql%rowcount;
  open c0 for select * from table(t0);
  open c1 for select * from table(t1);
  ? := c0;
  ? := c1;
end;

The difficulty is to have nominal TABLE types that must be generated ad-hoc for this query prior to executing the query.

An alternative is to use the PL/SQL TABLE types from DBMS_SQL to serialise the individual columns through individual SYS_REFCURSOR result values.

---

See also: #5190