Home » SQL & PL/SQL » SQL & PL/SQL » REF CURSOR problem
REF CURSOR problem [message #200531] Tue, 31 October 2006 04:58 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everybody,
i have this problem with ref cursor when i want to open it from plsql script;
i think the mean problem is how to open the cursor when using a call to that cursor within a procedure?
My code is:
First:
i create a package called 'pckitemdetails' to hold the ref cursor:
create or replace package pckitemdetails
as
type curitemdetails is ref cursor;
end;
Second: i create a procedure called 'pr_duplicatecontenttype' to do some work for me:
create or replace procedure pr_duplicatecontenttype
(
fromctcode in varchar
, toctcode in varchar
, toctnumber in pls_integer default 0
, rset out pckitemdetails.curitemdetails

) as ...

Finally:
i called the procedure within plsql script like this:
declare
c_cursor pckitemdetails.curitemdetails ;
r_ct Contenttypes.code%type;
begin
Pr_Duplicatecontenttype2 ('wfa', 'wfr', 0, c_cursor);

loop

fetch c_cursor into r_ct;
exit when c_cursor%notfound;
dbms_output.put_line(r_ct);
end loop;
close c_cursor;
exception
when others then
raise_application_error(-20001,'An error was encountered : '||sqlcode||' -error- '||sqlerrm);

end ;
/
show errors


ContentType with Code 'WFR' already exists in table ContentTypes.
begin
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "BF3010.TEST_CALL", line 7
ORA-06512: at line 2

So i have a duplicate value ,thats allwright ,but why the error of the invalid cursor??

Thanks in advance,
bahaa
Re: REF CURSOR problem [message #200542 is a reply to message #200531] Tue, 31 October 2006 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that the problem is in the procedure pr_duplicatecontenttype.

Are you sure that this procedure is opening the cursor correctly.
Re: REF CURSOR problem [message #200619 is a reply to message #200542] Tue, 31 October 2006 09:08 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi again,
i used this code and it works:

DECLARE
l_recset Pckitemdetails.CURITEMDETAILS;
BEGIN
Pr_Duplicatecontenttype (FROMCTCODE=>'WFA',TOCTCODE=> 'ttt',TOCTNUMBER=> 0,RSET=> l_recset);
end;
Thank you for your help,
bahaa
Previous Topic: query help
Next Topic: Sequence.currval problem
Goto Forum:
  


Current Time: Mon Dec 05 04:41:08 CST 2016

Total time taken to generate the page: 0.13985 seconds