Delphi / Oracle / Stored Proc

Delphi / Oracle / Stored Proc

Post by Marc Scheun » Thu, 06 Feb 1997 04:00:00



Has anyone out there created an Oracle stored procedures that returns
more than one row of data, and interfaced from Delphi to this stored
procedure?

I basically know how to create a stored procedure in Oracle that uses
a cursor to iterate through a set of rows.

I know (and have used) TStoredProc in Delphi, but so far I never tried
to have one return multiple values.

In Interbase, you can say something like

        for select (fields) from (table)
        where (condition)
        into (parameters)
        do
          suspend;

Is there something similar in Oracle? How can I make it clear to the
Oracle stored procedure that an "outside event" (e.g. a
"TStoredProc.Next" from Delphi) will fetch the next row of data?

Any hitns are welcome!
Marc

----------------------------------------------------------------------

 
 
 

Delphi / Oracle / Stored Proc

Post by Ed Hillma » Thu, 06 Feb 1997 04:00:00


From my understanding of Delphi's interface with Oracle, it cannot
return result sets from Oracle Stored procedures.  In fact, currently,
it can only return one set, and it is written in the Params property
of the TStoredProc component.  This is what I have been told, having
never experimented with it myself.  So, someone with more first hand
knowledge may know differently.

Currently, you cannot get a return set from an Oracle stored
procedure.  I don't think this is going to stay the same for much
longer.  It is my understanding that a) Oracle has modified their
Stored Procedures so they can return multiple rows, and b) the BDE
will be changed to get those multiple rows to Delphi.  I think a) has
happened.  I can't confirm nor deny if b) is going to be in the next
release of Delphi and/or the BDE.  It may be worthwhile to see if that
is mentioned when they officially release what will be in Delphi 97,
or Delphi 3.0 or whatever it's being called.

Good luck,
Ed


>Has anyone out there created an Oracle stored procedures that returns
>more than one row of data, and interfaced from Delphi to this stored
>procedure?
>I basically know how to create a stored procedure in Oracle that uses
>a cursor to iterate through a set of rows.
>I know (and have used) TStoredProc in Delphi, but so far I never tried
>to have one return multiple values.
>In Interbase, you can say something like

>    for select (fields) from (table)
>    where (condition)
>    into (parameters)
>    do
>      suspend;
>Is there something similar in Oracle? How can I make it clear to the
>Oracle stored procedure that an "outside event" (e.g. a
>"TStoredProc.Next" from Delphi) will fetch the next row of data?
>Any hitns are welcome!
>Marc
>----------------------------------------------------------------------



 
 
 

Delphi / Oracle / Stored Proc

Post by Jasper St » Thu, 06 Feb 1997 04:00:00



> From my understanding of Delphi's interface with Oracle, it cannot
> return result sets from Oracle Stored procedures.  In fact, currently,
> it can only return one set, and it is written in the Params property
> of the TStoredProc component.  This is what I have been told, having
> never experimented with it myself.  So, someone with more first hand
> knowledge may know differently.

To the best of my knowledge, it's impossible, but if someone has THE
solution, I'd sure like to hear about it!...

Quote:> Currently, you cannot get a return set from an Oracle stored
> procedure.  I don't think this is going to stay the same for much
> longer.  It is my understanding that a) Oracle has modified their
> Stored Procedures so they can return multiple rows, and b) the BDE
> will be changed to get those multiple rows to Delphi.  I think a) has
> happened.  I can't confirm nor deny if b) is going to be in the next
> release of Delphi and/or the BDE.  It may be worthwhile to see if that
> is mentioned when they officially release what will be in Delphi 97,
> or Delphi 3.0 or whatever it's being called.

ad a)   I heard rumors about Oracle 8 having it...
ad b)   I think Delphi is prepared, because it can get at the result set
produced by an Interbase stored proc (since Delphi 1.00 on... :-)), by
simple means of a select * from StoredProc(param,...)
> Good luck,
> Ed


> >Has anyone out there created an Oracle stored procedures that returns
> >more than one row of data, and interfaced from Delphi to this stored
> >procedure?

> >I basically know how to create a stored procedure in Oracle that uses
> >a cursor to iterate through a set of rows.

> >I know (and have used) TStoredProc in Delphi, but so far I never tried
> >to have one return multiple values.

> >In Interbase, you can say something like

> >       for select (fields) from (table)
> >       where (condition)
> >       into (parameters)
> >       do
> >         suspend;

> >Is there something similar in Oracle? How can I make it clear to the
> >Oracle stored procedure that an "outside event" (e.g. a
> >"TStoredProc.Next" from Delphi) will fetch the next row of data?

> >Any hitns are welcome!
> >Marc

> >----------------------------------------------------------------------


 
 
 

Delphi / Oracle / Stored Proc

Post by Victor Poteryahi » Fri, 07 Feb 1997 04:00:00


  The last BDE version can't handle oracle cursor variables as parameters,
so you can't return rowsets from Oracle stored procedures. More over it be
pretty tricky to Borland to fit oracle cursor variables in current
methaphor of TStoredProc, but they claim to support it in next release of
BDE. Now one of the way to work from Delphi 2.0 with oracle cursor
variables and PL/SQL tables as parameters is using third-party products,
but they is uncompatible with Borland data-aware components. I can send
more info in e-mail.

Regards, Victor Poteryahin.

 
 
 

Delphi / Oracle / Stored Proc

Post by Mart » Fri, 07 Feb 1997 04:00:00


Use PL/SQL tables to build the equivilent of your cursor. The create a
function for each operation you require, i.e. read a row, forward a row, back
a row.

Returning the values is then a matter of a select:

select func_readarow from dual;
select func_backarow from dual;

(or something similar ;)

etc.

Indepth info on PL/SQL tables is in the manuals.

Martin


 
 
 

1. Delphi / Oracle / Stored Proc

Has anyone out there created an Oracle stored procedures that returns
more than one row of data, and interfaced from Delphi to this stored
procedure?

I basically know how to create a stored procedure in Oracle that uses
a cursor to iterate through a set of rows.

I know (and have used) TStoredProc in Delphi, but so far I never tried
to have one return multiple values.

In Interbase, you can say something like

        for select (fields) from (table)
        where (condition)
        into (parameters)
        do
          suspend;

Is there something similar in Oracle? How can I make it clear to the
Oracle stored procedure that an "outside event" (e.g. a
"TStoredProc.Next" from Delphi) will fetch the next row of data?

Any hitns are welcome!
Marc

----------------------------------------------------------------------

2. Numeric Data Encryption - Any Ideas

3. Delphi + Oracle stored proc. ?

4. gthrsvcflt*.eml located in WinNT/Temp

5. Delphi 3 Oracle Stored Proc Problem

6. Stalled checkpoint

7. Stored Proc Calling Another Stored Proc

8. Sybase DBA and SUN platform

9. How to get results from Stored Proc within a Stored Proc

10. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

11. calling stored proc from stored proc via variable

12. Calling a stored proc within a stored proc

13. Calling Stored Proc from other Stored Proc