| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Returning Result Set In a SP
On Wed, 13 Aug 1997 13:40:22 GMT, dave_at_fifthd.ca (Dave Macpherson) wrote:
>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
I think that should be 7.2 or greater. cursor variables were introduced with the 7.2 engine.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |