Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursors
Ref Cursors [message #36458] Wed, 28 November 2001 23:48 Go to next message
Malli
Messages: 10
Registered: November 2001
Junior Member
Hi,
I am returning Records from PL/SQL Function With Ref cursors. This function is calling from JSP Page.
I have a doubt that the Ref Cursor is not closing after function executed. It is remaining in the data base. Does Ref Cursor should be closed or Ref Cursors automatically close after data retreival from result set.
pl clarify it for me.
Adv Tks
Malli

----------------------------------------------------------------------
Re: Ref Cursors [message #36471 is a reply to message #36458] Thu, 29 November 2001 04:48 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
It will automatically closes when it reaches last record.

----------------------------------------------------------------------
Re: Ref Cursors [message #37969 is a reply to message #36471] Mon, 11 March 2002 04:51 Go to previous messageGo to next message
sridhar
Messages: 119
Registered: December 2001
Senior Member
Suresh,
Do u have any idea how we can get a row count with the ref cursor ? Looks like the attribute %Rowcount doesnot work for ref cursors..

Thank you,
Sridhar
Re: Ref Cursors [message #37971 is a reply to message #36471] Mon, 11 March 2002 06:35 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
yes, rowcount will not work with ref cursors because ref cursors are not cursors. they are just pointers to cursor and also known as cursor variables. but you can get count of it in program which you open that ref cursor. if you are using in vb,java,asp then you can get result set count using resultset methods. in pl/sql, declare number variable and increment in loop, which u used to fetch refcursor values.

HTH
Suresh
Re: Ref Cursors [message #39210 is a reply to message #36471] Wed, 26 June 2002 07:35 Go to previous message
Daphne
Messages: 1
Registered: June 2002
Junior Member
I am a little confused, you say that %rowcount will not work with ref cursors but the following statement is contained in Oracle Documentation 'In the example below, you open the cursor variable emp_cv. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.

IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM emp;
END IF;

'
However when I try to do a %rowcount I always get a value of zero.
Basically what I am trying to do is check to see if there is any data returned in the refcursor, if so I return a response code of 0 otherwise a response code of 5. Here is an example of my code.

OPEN v_out_agent_details_list FOR
SELECT TYP_CD , uplift_factor, AGY_PCT
FROM agents asag
WHERE ref_key = v_ref_key;

IF v_out_agent_details_list%rowcount = 0 THEN
v_resp_code := 5;
ELSE
v_resp_code := 0;
END IF;

Any suggestions?
Daphne
Previous Topic: Create Function
Next Topic: nested sql statement
Goto Forum:
  


Current Time: Tue Oct 22 14:31:04 CDT 2019