| invalid cursor??? [message #40390] |
Thu, 03 October 2002 06:39  |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
I am getting invalid cursor on the line
fetch my_ref_1 bulk collect into my_type_b;
It looks right, does anyone have any ideas.
Below is entire code if anyone cares to test.
Thanks
Rick
drop table test_d;
drop table test_c;
drop table test_b;
drop table test_a;
create table test_a (id number);
create table test_b(id number, test_a_id number);
create table test_c(id number, test_a_id number);
create table test_d(id number, test_b_id number);
alter table test_a add constraint pk_test_a primary key(id);
alter table test_b add constraint pk_test_b primary key(id);
alter table test_c add constraint pk_test_c primary key(id);
alter table test_d add constraint pk_test_d primary key(id);
alter table test_b add constraint fk_test_a_test_b foreign key(test_a_id)
references test_a(id);
alter table test_c add constraint fk_test_a_test_c foreign key(test_a_id)
references test_a(id);
alter table test_d add constraint fk_test_b_test_d foreign key(test_b_id)
references test_b(id);
insert into test_a(id) values (1);
insert into test_a(id) values (2);
insert into test_b(id, test_a_id) values (1, 1);
insert into test_b(id, test_a_id) values (2, 1);
insert into test_b(id, test_a_id) values (3, 1);
insert into test_c(id, test_a_id) values (1, 1);
insert into test_c(id, test_a_id) values (2, 1);
insert into test_d(id, test_b_id) values (1, 1);
commit;
select count(1) from test_a;
select count(1) from test_b;
select count(1) from test_c;
select count(1) from test_d;
DECLARE
type ref_cur is ref cursor;
my_ref_1 ref_cur;
cursor c1 is select id from test_a;
type a_type is table of test_a.id%type;
type b_type is table of test_b.id%type;
my_type_a a_type;
my_type_b b_type;
nCount binary_integer;
BEGIN
open c1;
fetch c1 bulk collect into my_type_a;
for nCount IN my_type_a.FIRST..my_type_a.LAST loop
delete from test_c where test_a_id = my_type_a(nCount);
open my_ref_1 for 'select id from test_b where test_a_id ='||my_type_a(nCount);
fetch my_ref_1 bulk collect into my_type_b;
for nCount IN my_type_b.FIRST..my_type_b.LAST loop
delete from test_d where test_b_id = my_type_b(nCount);
end loop;
close my_ref_1;
end loop;
close c1;
END;
/
|
|
|
|
| Re: invalid cursor??? [message #40393 is a reply to message #40390] |
Thu, 03 October 2002 08:53  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
You cannot bulk collect from a weak ref cursor in 8i (you can in 9i). Also, you should not explicitly declare the nCount variable - it is declared implicitly as part of the loop, and you shouldn't use the same name for the loop index in both of the loops since one of them is nested.
|
|
|
|
| Re: invalid cursor??? [message #41177 is a reply to message #40390] |
Wed, 04 December 2002 10:52 |
CAS
Messages: 5
Registered: December 2002
|
Junior Member |
|
|
I have come across an invalid cursor error while using a loop in some PRO C code. We dynamically build a cursor with a bind variable. However, we are dynamically building a new cursor several times and the Error number 1001 indicated that maybe too many cursors were open at once. The one possible being called upon had been bumped out of the cache in oracle, therefore making it an invalid cursor to fetch from. We alleviated the problem by raising the MAXOPENCURSOR limit to 50 from 10. We added the line
MAXOPENCURSORS=50 in the make file as a compliation option.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
