Cursor returns no records

I tried with FOR loop, still it does not return data.

Can you copy code and Table Structure?
Based on above details may be we can find some solution

-Sandeep

declare

variable_name  varchar2(40);
cursor c1 is select column1 from xyz;

begin

open c1;
loop
fetch c1 into variable_name;
dbms_output.put_line(variable_name);
exit when c1%notfound;
end loop;

close c1;


end;
/


try this out

amintushar23, I tried that but it does not return data.

The query returns data in sql navigator.

I found another interesting thing! The same query does not return data if I run in sql plus.

Any information or links on sql navigator vs sql plus!

Hi vnkurra,

I had experienced the same problem, but could never find the cause, ofcourse, i found the solution..

Solution:

Declare

   Cursor c1 is Select column1 from table;

   v_c1 c1%rowtype;

BEGIN

   FOR v_c1 in (Select column1 from table)

     ...statements...
     ...statements...

      Exit When v_c1%notfound

   END LOOP;

END;


Aalap Sharma

Sorry, you dont have to write 'Exit When' statement in FOR LOOP.. it's a mistake...


FOR v_c1 in (Select column1 from table) LOOP

     ...statements...
     ...statements...

END LOOP;




Aalap Sharma

Hi Aalap Sharma,

Cursor returns data if I execute the procedure from SQL Navigator. It does NOT if I execute from SQL Plus. Any ideas!

Both sessions are connected to same schema using same user id.

hey vnkurra,

Can you please tell me what is the error that you are getting?
Try showing data on screen using DBMS_OUTPUT.PUT_LINE, in every iteration in the loop.

The first problem that you told me was encountered by me before sometime, and then i changed the code to the above one. It worked. Now what you are saying, is something, different.
I'll have to review the code for further analysis.

I think you will have to use the DBMS_OUTPUT approach to analyse further.

Aalap Sharma :)

Aalap,

I am not getting any error. I have tried the print statement in the loop, it exits the loop in the first iteratioon since the cursor does not return any data. Please note that this issue is in SQL PLUS. The same cursor returns data from SQL Navigator.

Why the cusor does not return data from SQL PLUS! Any ideas on SQL PLus vs SQL Navigator.

Thanks.

hello vnkurra,

did you try the cursor with the FOR loop?
I think this will definitely work on sqlplus also. I tried with this one and it works fine.

Try to change the code to the FOR LOOP cursor. The architecture is given in my previous post.

Aalap Sharma :)

the problem is when user starts sqlplus session the parameter "set serveroutput is set to off" by default

just add the parameter set serveroutput on   in c:\oracle\ora92\sqlplus\admin\glogin.sql

and see..............................


bye

tushar

hello vnkurra,

Hey.. I tried with the cursor in sqlplus.. and it is working fine.. i dont know what is the problem with your cursor..
I m getting all the things correctly..
Before running the procedure.. try setting the serveroutput on..

That's the last thing we can do..

Aalap Sharma :)