I keep getting invalid cursor.
I run the sql in the cursor it returns me a legit result sets.
--drop table emp
--drop table new_emp
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) )
--drop type myTableType
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;
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;