Re: Foolish question regarding ref cursors

From: Richard S. Crawford <rscrawfordDUCK_at_mossREMOVEWATERFOWLroot.com>
Date: Fri, 12 Mar 2004 10:55:43 -0800
Message-ID: <c2t14m$2of$1@woodrow.ucdavis.edu>

Galen Boyer wrote:

> On Thu, 11 Mar 2004, rscrawfordDUCK_at_mossREMOVEWATERFOWLroot.com
> wrote:
>

>>Galen Boyer wrote:
>>
>>
>>>On Wed, 10 Mar 2004,
>>>rscrawfordDUCK_at_mossREMOVEWATERFOWLroot.com wrote:
>>>
>>>
>>>>I have a Cold Fusion page which calls an Oracle Stored
>>>>Procedure, which in turn returns a ref cursor containing a
>>>>result set.  I've discovered that if I close the cursor before
>>>>returning to Cold Fusion, then Cold Fusion does not have
>>>>access to the result set.
>>>
>>>Not sure why you thought differently.
>>>
>>>
>>>>My question is, is it prudent to call another stored procedure
>>>>after Cold Fusion has the result set that will close the ref
>>>>cursor?  Or is it okay to just leave it open?
>>>
>>>You need to iterate over the result set doing whatever you
>>>need to do and then close it.
>>>
>>
>>I'm not really doing anything with the result set in the stored
>>procedure.  The job of the stored procedure is to pull up a
>>result set which it passes back to Cold Fusion.  

>
>
> But you are doing something with the stored procedure. You are
> using its results to load up memory within cold fusion. You have
> to explicitly retrieve those results. They don't just somehow
> transfer themselves to Cold Fusion.
>
> After retrieval, then close.
>

I'm sorry. I confess that I'm lost now. It seemed that opening the cursor and running select was sufficient to make the result set available for Cold Fusion to act on. Is there something else that I should be doing?

Here, for the sake of completeness, is the stored procedure in question:

---

create or replace procedure dlc_sp_getStudentInfo (

   studentID IN number,
   studentInfo IN OUT types.cursorType,
   courseInfo IN OUT types.cursorType
)

   as

   BEGIN    OPEN studentInfo FOR

• Part One: get student name, etc., and whether they've done the orientation SELECT sFirst, sLast, sOrient FROM tblStudentInfo WHERE sid = studentID;
• Part Two: get their other class info.

   OPEN courseInfo FOR

	SELECT
	  x.xID,
	  x.xName,
	  x.xURL,
	  n.nID,
	  r.lStartDate,
	  r.lEndDate,
	  n.iID,
	  i.iFirst,
	  i.iLast
	FROM
   	  tblCourses x,
   	  tblRoster r,
   	  tblSections n,
   	  tblInstructors i
	WHERE
	  x.xID = n.xID AND
	  r.nID = n.nID AND
   	  r.sID = studentID AND
   	  n.iID = i.iID AND
   	  r.lStartDate < current_date;
	

   END dlc_sp_getstudentInfo;

---

Once this stored procedure has been called, I can refer to the ref cursor in Cold Fusion to get the results and use Cold Fusion to build a web page using the data.

If I add the line "CLOSE courseInfo" or "CLOSE studentInfo", I get an error message when I try to call the stored procedure from Cold Fusion.

Is there something more I should be doing? Received on Fri Mar 12 2004 - 12:55:43 CST