EXECUTE IMMEDIATE with USING and OUT Parameter

If you want to use an OUT – Parameter in an EXECUTE IMMEDIATE – Statement including an anonymous PL/SQL-Block: here is an example:

create table in_out_test ( num_ber number);


set serveroutput on;
DECLARE
vc_sql CLOB;
p_insert_count NUMBER;
BEGIN
vc_sql := '
DECLARE
cursor curData is
with resNmb(iNmb) as
(select 1 as iNmb from dual
union all
select iNmb + 1 from resNmb where iNmb < 1005
)
select iNmb as num_ber from resNmb;
type typeTblData is table of curData%ROWTYPE;
tblData typeTblData;
iterator NUMBER := 0;
insert_count NUMBER;
begin
open curData;
loop
fetch curData bulk collect into tblData limit 100;
exit when tblData.COUNT = 0;
forall ii in tblData.FIRST..tblData.LAST
INSERT INTO in_out_test
values tblData(ii) ;
insert_count := (iterator*100)+Sql%Rowcount;
:1 := insert_count;
commit;
iterator := iterator +1;
end loop;
close curData;
exception
when others then
rollback;
if curData%ISOPEN then close curData; end if;
raise;
end;
';
execute immediate vc_sql using out p_insert_count;
dbms_output.put_line('IN OUT PARAMETER: '||p_insert_count||' rows inserted');
END;
/

>> PL/SQL procedure successfully completed.
>> IN OUT PARAMETER: 1005 rows inserted

drop table in_out_test purge;

Schreibe einen Kommentar