Board index » delphi » D3/Oracle7.3/Stored Proc Result Sets

D3/Oracle7.3/Stored Proc Result Sets

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.

Regards,
Dave Macpherson

 

Re:D3/Oracle7.3/Stored Proc Result Sets


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

======================================================================
[ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
======================================================================

Re:D3/Oracle7.3/Stored Proc Result Sets


Oracle 7.3 DOES support this and we added support in BDE SQL Links
to support this. Check out Delphi 3/Client Server..

Vijay
Borland

Quote
Marc Scheuner wrote:

> 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

> ======================================================================
> [ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
> ======================================================================

Re:D3/Oracle7.3/Stored Proc Result Sets


Quote
Narayanan Vijaykumar <nvi...@borland.com> wrote:
>Oracle 7.3 DOES support this and we added support in BDE SQL Links
>to support this. Check out Delphi 3/Client Server..

Sorry, I don't have access to D3 C/S, but could you provide a sample
Oracle stored proc showing how to do it?

Also, is there any way at all to use this new Oracle feature in
previous Delphi versions, esp. Delphi 1 ??

Marc

======================================================================
[ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
======================================================================

Re:D3/Oracle7.3/Stored Proc Result Sets


On Tue, 27 May 1997 13:19:56 -0700, Narayanan Vijaykumar

Quote
<nvi...@borland.com> wrote:
>Oracle 7.3 DOES support this and we added support in BDE SQL Links
>to support this. Check out Delphi 3/Client Server..

>Vijay
>Borland

>Marc Scheuner wrote:

>> 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

>> ======================================================================
>> [ Marc Scheuner, marc.scheu...@berner.ch  CH-3001 BERNE, Switzerland ]
>> ======================================================================

Sorry, perhaps I should have posed my question a little bit
differently. I know that D3 SQL links supports this, I was simply
asking HOW I would do it. I guess I was looking for a coding example.
Anyway, someone else emailed me with an example, so now I'm happy.

What are Borland's plans regarding SQL Links and the new features to
be found in Oracle8?

Regards,
Dave Macpherson

Re:D3/Oracle7.3/Stored Proc Result Sets


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

Other Threads