Re: Returning Result Set In a SP

On Thu, 07 Aug 1997 17:10:41 +0100, Richard Ayeni <RichardA_at_chemware.co.uk> wrote:

>Hi, everyone.
>I'm Richie.
>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.
>
>I need your help.
>
>- 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;
>---------------------------------------------------------
>Richard Ayeni
>
>RichardA_at_chemware.co.uk

How to return a result set from Oracle stored procedure to Delphi  

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.  

 I found this example on the Crystal Report Web Site  

 Hope this helps

Regards,
Dave Macpherson Received on Fri Aug 08 1997 - 00:00:00 CDT