In article ,
marc.scheu...@NO.SPAM.PLEASE.ch (Marc Scheuner) wrote:
Quote
> d...@fifthd.ca (Dave Macpherson) wrote:
> >I hear that you can return result sets with Oracle 7.3 in Delphi3, but
> >damned if I know how to code the stored proc to do this. Do you need
> >to return a cursor variable? How do you handle this in Delphi3? Anyone
> >have any examples? The D3 help file says that you can do this with
> >Oracle 7.3 but I can't find any examples.
> No, in Oracle you CANNOT return result sets from stored procs. It's
> not a Delphi problem, Oracle simply doesn't support it.
> Oracle promises it for Oracle 8, but don't hold your breath waiting
> for it......
> Marc
Sorry Marc, not quite accurate! I have done it in Delphi 3.0 with SQL
Links and a 7.3 database.
Here is what you have to do: (I don't take any credit for this, I found
it on the Borland newsgroups @ fourums.borland.com)
In Oracle 7.3 you have to create a package spec:
Package CURSOR_TYPES
IS
Type People_Cur is ref cursor return people%ROWTYPE;
end;
This is the recordset that you wish to return...
The following is an example of a stored procedure that will return a
recordset.
procedure Test_Return(Retval in out cursor_types.People_Cur)
is
begin
open RetVal for Select * from people;
end;
That is what is to be done on the ORACLE side... On the Delphi side, you
basically need a Datasource, and Stored Proc component.
In the Stored Proc, select the Test_Return from the "Stored Procedures"
and click on the Params. Change the "DATA TYPE" to cursor, and make sure
that "Parameter Type" is "IN/OUT". Connect everything together, and you
can link the datasource to a Data bound grid, and see the results!
This also assumes that you have a good connection to an Oracle 7.3
database. This will not work with Oracle 7.2, only 7.3 can return a
recordset!
Hope this helps!
TTYL
Derrick
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
