Home » SQL & PL/SQL » SQL & PL/SQL » REF Cursor Issue (Oracle 8i)
REF Cursor Issue [message #331379] Thu, 03 July 2008 03:50 Go to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi ,
I have a strange issue when i changed my program using a cursor to ref cursor.I can see the last record in my ref cursor gets printed twice,only the last record.Here is my sample code,
DECLARE
TYPE c_ref is ref cursor;
c_ref1 c_ref;
v_no number :=1;
v_no1 number;
v_no2 number;
v_no3 number;
v_str varchar2(50);
BEGIN
v_str:=' select 1,2,3 from dual Order by 1';
    if v_no=1 then
        dbms_output.put_line('v_no is 1');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;        
    else
         dbms_output.put_line('v_no is XXX');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;
    end if;
    LOOP
       EXIT WHEN c_ref1%notfound;
       FETCH c_ref1 into v_no1,v_no2,v_no3;
       dbms_output.put_line('REsult : '||v_no1||v_no2||v_no3);
    END LOOP;
    CLOSE c_ref1;
END; 

This result as
v_no is 1
REsult : 123
REsult : 123

Does anyone have an idea on the cause of issue?
Regards
Ramya

[Mod-edit: Frank added [code]-tags to improve readability]

[Updated on: Thu, 03 July 2008 03:57] by Moderator

Report message to a moderator

Re: REF Cursor Issue [message #331385 is a reply to message #331379] Thu, 03 July 2008 03:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The order of fetch, exit and print are wrong.
Now you fetch, print (even if nothing was fetched) and THEN exit, in the next iteration.

Actually I would expect an error, since you are fetching beyond the end of the cursor..
Re: REF Cursor Issue [message #331393 is a reply to message #331379] Thu, 03 July 2008 04:11 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi Frank,
Thanks for your response.
I did tried the order as you said,and did changed the program as below
DECLARE
TYPE c_ref is ref cursor;
c_ref1 c_ref;
v_no number :=1;
v_no1 number;
v_no2 number;
v_no3 number;
v_str varchar2(50);
BEGIN
v_str:=' select 1,2,3 from dual Order by 1';
    if v_no=1 then
    dbms_output.put_line('v_no is 1');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;        
    else
    dbms_output.put_line('v_no is XXX');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;
    end if;
    LOOP    
    [b]FETCH c_ref1 into v_no1,v_no2,v_no3;
    dbms_output.put_line('REsult : '||v_no1||v_no2||v_no3);
    EXIT WHEN c_ref1%notfound;
    END LOOP; [/b]   
    CLOSE c_ref1;
END; 

Suprisingly I dont see any Error and still get the same output Sad as
v_no is 1
REsult : 123
REsult : 123
No Clue clicks to me Sad


[Mod-edit: Frank added code-tags AGAIN]

[Updated on: Thu, 03 July 2008 04:35] by Moderator

Report message to a moderator

Re: REF Cursor Issue [message #331400 is a reply to message #331393] Thu, 03 July 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: REF Cursor Issue [message #331405 is a reply to message #331393] Thu, 03 July 2008 04:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you not see that I edited your first message? Please add code-tage around your code next time.
Again, you make the same mistake. You print after the fetch, and only then exit.
Re: REF Cursor Issue [message #331419 is a reply to message #331385] Thu, 03 July 2008 04:54 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi Frank,
I will format the code hereafter,my apologies for that.
Frank wrote on Thu, 03 July 2008 03:59
The order of fetch, exit and print are wrong.
Now you fetch, print (even if nothing was fetched) and THEN exit, in the next iteration.

Actually I would expect an error, since you are fetching beyond the end of the cursor..


I have changed the code as you said isn't?
[Fetch] FETCH c_ref1 into v_no1,v_no2,v_no3;
[Print] dbms_output.put_line'REsult : '||v_no1||v_no2||v_no3);
[Exit] EXIT WHEN c_ref1%notfound;


Re: REF Cursor Issue [message #331421 is a reply to message #331419] Thu, 03 July 2008 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The order needs to be:

1) Attempt to Fetch a record

2) Check to see if %NOTFOUND is set, indicating that your previous fetch got the last row in the cursor. Exit if this is the case.

3) Any other processing.

4) Back to 1)
Re: REF Cursor Issue [message #331436 is a reply to message #331419] Thu, 03 July 2008 05:40 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi ramya,
The order should be FETCH,EXIT,PRINT.
Check below code.
DECLARE
TYPE c_ref is ref cursor;
c_ref1 c_ref;
v_no number :=1;
v_no1 number;
v_no2 number;
v_no3 number;
v_str varchar2(50);
BEGIN
v_str:=' select 1,2,3 from dual Order by 1';
    if v_no=1 then
    dbms_output.put_line('v_no is 1');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;        
    else
    dbms_output.put_line('v_no is XXX');
        OPEN c_ref1 FOR
        select 1,2,3 from dual;
    end if;
    LOOP    
   FETCH c_ref1 into v_no1,v_no2,v_no3;
    EXIT WHEN c_ref1%notfound;
	dbms_output.put_line('REsult : '||v_no1||v_no2||v_no3);
    END LOOP; 
    CLOSE c_ref1;
END; 


Regards,
Pointers.
Re: REF Cursor Issue [message #331437 is a reply to message #331436] Thu, 03 July 2008 05:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And only 33 minutes after I said the same thing... Cool
icon14.gif  Re: REF Cursor Issue [message #331440 is a reply to message #331421] Thu, 03 July 2008 05:49 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hello JRowBottom,
Tons of thanks,It did solved the issue after I changed the code as you said to
FETCH c_ref1 into v_no1,v_no2,v_no3;
EXIT WHEN c_ref1%notfound;
dbms_output.put_line('REsult : '||v_no1||v_no2||v_no3);
END LOOP;

This gives me the right output.
Re: REF Cursor Issue [message #331442 is a reply to message #331440] Thu, 03 July 2008 05:53 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still don't know how to format your code.

Regards
Michel
Previous Topic: insert in query ?
Next Topic: how to reset all sequences at a time?
Goto Forum:
  


Current Time: Thu Dec 08 00:00:53 CST 2016

Total time taken to generate the page: 0.22722 seconds