db version 11.1.0.7
trying to do a returning bulk collect but it is not working:
-- my test table
create table t as
with f as (select rownum rn from dual connect by rownum <= 10)
select
rn,
lpad('x',10,'x') pad
from f;
-- works as expected
declare
type aat is table of t%rowtype;
aay aat;
begin
delete from t returning rn,pad bulk collect into aay;
rollback;
end;
/
-- but the table I really want to do has many columns so I want to dynamically build list of columns for the
-- returning clause. This way if the table changes the stored proc will not have to be modified
-- fails PLS-00429: unsupported feature with RETURNING clause
declare
type aat is table of t%rowtype;
aay aat;
s varchar2(4000);
begin
s := 'delete from t returning rn,pad into :1';
execute immediate s returning bulk collect into aay;
rollback;
end;
/
-- tried a few other things:
create or replace type t_obj as object (rn number,pad varchar2(10));
/
-- PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
declare
nt t_obj;
s varchar2(4000);
begin
s := 'delete from t returning t_obj(rn,pad) into :1';
execute immediate s returning bulk collect into nt;
rollback;
end;
/
-- works, but would require store proc changes if the table changes
declare
type t is table of number;
type v is table of varchar2(10);
vt v;
nt t;
s varchar2(4000);
begin
s := 'update t set rn = 10 returning rn,pad into :1,:2';
execute immediate s returning bulk collect into nt,vt;
rollback;
end;
/
basically I want to dynamically build the list of columns with all_tab_cols and put the list into the returning clause
but seems like I will have to hard code the column lists. This means whenever the table changes I will have to
modify the store proc .. Any way around this?
Thanks