Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to debug invalid cursor while using object

kaericnMay 9 2018 — edited May 9 2018

Heres a simple version of my plsql code using bulk collect merge and object

I keep getting invalid cursor.

How do I debug this invalid cursor that uses object??

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;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 6 2018
Added on May 9 2018
8 comments
282 views