Re: Returning Result Set In a SP

On Tue, 12 Aug 1997 19:19:43 -0500, Tomm Carr <tommcatt_at_geocities.com> wrote:

>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

How to return a result set from Oracle stored procedure to Delphi (requires Oracle 7.3 or greater)  

1. Create a package with a cursor definition

 CREATE OR REPLACE PACKAGE SCOTT.cursor_types IS    TYPE empcur IS REF CURSOR RETURN emp%ROWTYPE;  END;  2. Create a stored procedure
 CREATE OR REPLACE PROCEDURE
 SCOTT.getallemployees(emp_cv in out cursor_types.EmpCur) IS BEGIN   open emp_cv for select * from emp;
 END;    3. Create a TStoredProc object in Delphi 3.0 for the procedure  SCOTT.getallemployees  

 4. Set the parameter EMP_CV to the value Cursor  

 5. Link the TStoredProc to a TDataSet  

 6. Open the StoredProc  

 Now you're done! The result set is returned via Oracle to your Delphi app.    

 Hope this helps

Regards,
Dave Macpherson Received on Wed Aug 13 1997 - 00:00:00 CDT