I have the following code based on asktom to create this example and the goal is to be able to control the commit size while doing merge.
we find the following code that does not need to create an object type but the code example is not using cursor
Is it possible to NOT create a seperate object while still using cursor.
create table emp as select object_id as empno, object_name as ename from user_objects;
select max(length(object_name)) from user_objects
select count(*) from emp
create table new_emp as select * from emp where 1 = 0
create or replace type myScalarType as object
( empno number, ename varchar2(300) )
create or replace type myTableType as table of myScalarType
--delete from emp
--delete from new_emp
select * from new_emp
select * from emp
declare
l_data myTableType;
l_limit number default 5;
lv_row_cnt number := 0;
--type t_new_emp is table of emp%rowtype;
--t_new_emp_tab t_new_emp;
cursor c is select myScalarType(empno,ename) from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit l_limit;
if ( l_data.count > 0 )
then
--for i in 1 .. l_data.count
--loop
--l_data(i).ename := initcap( l_data(i).ename );
--end loop;
merge into new_emp
using ( select * from table(cast(l_data as myTableType))) X
on (new_emp.empno = x.empno)
when matched then update set ename = x.ename
when not matched then insert ( empno, ename ) values ( x.empno, x.ename );
end if;
exit when c%notfound;
dbms_output.put_line('commit '||to_char(systimestamp));
dbms_output.put_line('l_data.count '||l_data.count);
select count(*) into lv_row_cnt from new_emp;
dbms_output.put_line('lv_row_cnt '||lv_row_cnt);
commit;
end loop;
dbms_output.put_line('commitxx '||to_char(systimestamp));
dbms_output.put_line('l_data.countxx '||l_data.count);
select count(*) into lv_row_cnt from new_emp;
dbms_output.put_line('lv_row_cntxx '||lv_row_cnt);
end;
without create seperate object type but not using cursor and not sure if it can control the commit size ?
asktom example that uses cursor but create separate object type but can control commit size