Re: Returning Result Set In a SP

Richard Ayeni wrote:
>
> I have a question about delphi/Oracle Storedprocedure.
> I wrote StoredProcedure in Oracle is meant to returns a result set.
> How do i extract the data in the out statment for each record.
>
> - Oracle StoredProcedure source:
> ---------------------------------------------------------
> CREATE OR REPLACE PROCEDURE sel_rows2
> (v_id OUT huser.chkuser.k_id%TYPE,
> v_kname OUT huser.chkuser.kname%TYPE)
> IS
> CURSOR c1 IS
> SELECT k_id, kname
> FROM huser.chkuser
> WHERE rownum between 1 and 100;
> id huser.chkuser.k_id%TYPE;
> name huser.chkuser.kname%TYPE;
> BEGIN
> OPEN c1;
> LOOP
> FETCH c1 INTO id, name;
> EXIT WHEN c1%NOTFOUND;
> v_id := id;
> v_kname := name;
> END LOOP;
> CLOSE c1;
> END sel_rows2;

No, no, no. Oracle SPs do not return result sets! Your out parameters will continually be overridden and will end up with the last values fetched. The simplest method of accomplishing what you are trying for here is to use a TQuery object and place the select statement in it.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"