Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Sys_refcursor (Oracle 10g)
Problem with Sys_refcursor [message #582372] Wed, 17 April 2013 13:02 Go to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Hi Experts!

I'm having an issue with an Oracle ref_cursor, someone can tell me how to call the cursor within an anomimous Pl/Sql Block. I did it once but I can't get it working now.


DECLARE

v_cursor   sys_refcursor;
t_cursor   v_cursor%rowtype;

BEGIN

OPEN v_cursor FOR
select *
from customers;
LOOP
    FETCH v_cursor into t_cursor;
    EXIT WHEN v_cursor%notfound;
end loop;    

END;



Thanks in advance!
Regards,
Steve
Re: Problem with Sys_refcursor [message #582373 is a reply to message #582372] Wed, 17 April 2013 13:06 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to type t_cursor to customers not v_cursor. V_cursor doesn't have defined columns in the declare section.
Re: Problem with Sys_refcursor [message #582374 is a reply to message #582373] Wed, 17 April 2013 13:10 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Thanks a lot. Problem Solved!!

DECLARE

v_cursor   sys_refcursor;
t_cursor   customers%rowtype;

BEGIN

OPEN v_cursor FOR
select *
from customers;
LOOP
    FETCH v_cursor into t_cursor;
    dbms_output.put_line(customers.user_id);
    EXIT WHEN v_cursor%notfound;
end loop;    

END;



Regards,
Steve
Re: Problem with Sys_refcursor [message #582375 is a reply to message #582372] Wed, 17 April 2013 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also create a strongly typed ref cursor:
SQL> DECLARE
  2   type typ is ref cursor return emp%rowtype;
  3   v_cursor typ;
  4   t_cursor   v_cursor%rowtype;
  5  begin
  6   open v_cursor for select * from emp;
  7   loop
  8     FETCH v_cursor into t_cursor;
  9     EXIT WHEN v_cursor%notfound;
 10   end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: Problem with Sys_refcursor [message #582376 is a reply to message #582375] Wed, 17 April 2013 13:25 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Thank you Sir. I will consider that option.

Regards,
Stee.
Re: Problem with Sys_refcursor [message #582412 is a reply to message #582374] Thu, 18 April 2013 04:15 Go to previous message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

DECLARE

v_cursor   sys_refcursor;
t_cursor   customers%rowtype;

BEGIN

OPEN v_cursor FOR
select *
from customers;
LOOP
    FETCH v_cursor into t_cursor;
    --dbms_output.put_line(customers.user_id);this is not going to work   
    EXIT WHEN v_cursor%notfound;
    dbms_output.put_line(t_cursor.user_id);
end loop;    

END;

[Updated on: Thu, 18 April 2013 04:20]

Report message to a moderator

Previous Topic: Query help
Next Topic: Append more rows in single clob column
Goto Forum:
  


Current Time: Tue Sep 02 18:21:50 CDT 2014

Total time taken to generate the page: 0.09104 seconds