Home » SQL & PL/SQL » SQL & PL/SQL » REF CURSORS (10g)
REF CURSORS [message #442450] Mon, 08 February 2010 03:34 Go to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
Hi,

I seem to be going around in circles. can someone post a small example of how to achieve the following.

I want to call a procedure which has as an output paramater a ref cursor.

Then from the calling code I want to display the contents of that cursor.

Ive tried the obvious so must be doing something wrong.

Please advise

Thanks
Joe
Re: REF CURSORS [message #442451 is a reply to message #442450] Mon, 08 February 2010 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you post the code you've tried and we'll tell you where you're going wrong.
Re: REF CURSORS [message #442453 is a reply to message #442450] Mon, 08 February 2010 04:03 Go to previous messageGo to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
CREATE OR REPLACE PACKAGE "JUST_A_TEST" AS
TYPE out_cursor IS ref cursor;

PROCEDURE test_return_cursor(p_item_list in varchar2
,p_source in
varchar2
,x_hardwareitemcur out
out_cursor);
END NLPRC_SALESORDER_PKG;
/

CREATE OR REPLACE PACKAGE BODY "JUST_A_TEST" AS

PROCEDURE test_return_cursor (p_item_list
in varchar2
,p_source in
varchar2
,x_hardwareitemcur out
out_cursor)
IS
BEGIN
open x_hardwareitemcur for
select * from emp_names;
exception
when others then
IF x_hardwareitemcur%isopen THEN
close x_hardwareitemcur;
END IF;
dbms_output.put_line ('Error: ' || sqlerrm);
END checkhardwareproduct;

END "JUST_A_TEST";

/


To TEST:

declare
TYPE testP_REFCUR IS REF CURSOR;
test_CURVAR testP_REFCUR;
item_list varchar2(30) := '1,2,3,4';
rrr varchar2(500);

TYPE emp_rec is RECORD
(name varchar2(50)
,surname varchar2(50)
..
..
..);

begin
JUST_A_TEST.test_return_cursor(item_list,'TEST',test_CURVAR);
-- This is where i want to output the contents of the cursor.
OPEN test_curvar;
LOOP
FETCH test_curvar INTO emp_rec;
EXIT WHEN test_curvar%NOTFOUND;
dbms_output.put_line('Name: '|| emp_rec.name || 'Surname: '||
emp_rec.surname)
END LOOP;
-- Close cursor from calling code?
CLOSE test_curvar

end;



Re: REF CURSORS [message #442456 is a reply to message #442450] Mon, 08 February 2010 04:15 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Did you Google ?
Re: REF CURSORS [message #442458 is a reply to message #442453] Mon, 08 February 2010 04:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't open the cursor in your calling procedure; it is opened in the called procedure.
You are correct that the caller is responsible for closing it.
Re: REF CURSORS [message #442459 is a reply to message #442450] Mon, 08 February 2010 04:35 Go to previous messageGo to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
Thanks Guys,

Simple logic error.

All clear and working now!
Re: REF CURSORS [message #442463 is a reply to message #442450] Mon, 08 February 2010 05:00 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably worth noting that there is a simple way to test ref cursors using sqlplus:
SQL> create or replace procedure ref_cur_test (p_ref_cur out sys_refcursor)
  2  as
  3  begin
  4  open p_ref_cur for select * from dual;
  5  end;
  6  /

Procedure created.

SQL>  var l_ref refcursor
SQL> exec ref_cur_test (:l_ref);

PL/SQL procedure successfully completed.

SQL> print l_ref;

D
-
X
Re: REF CURSORS [message #442467 is a reply to message #442463] Mon, 08 February 2010 05:20 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Even thats a good example !

sriram
Previous Topic: SQL query
Next Topic: Parsing comma separated values into variables. (merged 6)
Goto Forum:
  


Current Time: Sat Dec 03 14:11:19 CST 2016

Total time taken to generate the page: 0.10370 seconds