Getting Result set from Oracle Stored procedure

Andrew Mannerin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Getting Result set from Oracle Stored procedure

We're in the process of running a "proof-of-concept" porting a subset of one
of out applications from Sybase 11.x to Oracle 8.x.
One of the features we've relied heavily on in Sybase is the ability to
return a result set from within a stored procedure as if it where a normal
select. So in a TQuery component the procedure is run by submitting an "EXEC
Proc @Foo = 1" SQL string, and retrieving the results in the "normal" way
from the TQuery, as if a select had been executed.
However in Oracle this doesn't seem to be the case. The consulting company
that did the port of the TSQL to PL/SQL substituted cursor variables in the
parameter list of the procs to emulate this, but I can find no way of
getting this to work within Delphi.

Simple Oracle Proc -

CREATE OR REPLACE PROCEDURE Pr_GetPortfolios
(p_PortfCV IN OUT CursorVarDef.PortfCursorType) IS
BEGIN
--
OPEN p_PortfCV FOR
SELECT PfCode, PfDesc
FROM Portfolios;
--
END Pr_GetPortfolios;

Supposedly equivilant to Sybase -

create procedure Pr_GetPortfolios
begin
select PfCode, PfDesc
from Portfolios
end

Is it possible to use the TQuery for these or is does TStoredProc support
this sort of thing? And how do you drive it anyway?

Any pointers gratefully appreciated.

Andrew Mannering
Sybase DBA

Wayne Menz

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Wayne Menzie wrote in <8F51AFF4Bwayneshammalammad...@207.105.83.65>:

Quote

>See this excellent post by Thomas Kyte.

Also add this page to your bookmark list: http://osi.oracle.com/~tkyte/

--
Wayne Menzie

Wayne Menz

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Andrew Mannering wrote in <8i37vj$9...@bornews.borland.com>:

Quote

>We're in the process of running a "proof-of-concept" porting a subset of
>one of out applications from Sybase 11.x to Oracle 8.x.
>One of the features we've relied heavily on in Sybase is the ability to
>return a result set from within a stored procedure as if it where a
>normal select.

See this excellent post by Thomas Kyte.
http://x56.deja.com/=dnc/getdoc.xp?AN=633460443&CONTEXT=960838953.145...
2&hitnum=1

The last part of the post seems particularly relevant.

--
Wayne Menzie

John Pierc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Quote

Andrew Mannering wrote:

> Is it possible to use the TQuery for these or is does TStoredProc support
> this sort of thing? And how do you drive it anyway?

> Any pointers gratefully appreciated.

> Andrew Mannering
> Sybase DBA

You use a stored proc. Pick the refcursor as a parameter of type cursor, in
out.
Then OPEN the SP and read the records in a loop just as you would do with a
tquery. Be sure to close the SP at the end.

John Pierce

Andrew Mannerin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Thanks for this - I can now see the output of the proc using the TStoredProc
components (once I'd "discovered" the implicit commit feature of Oracle that
made new rows invisible...)

However, is there any way at all of getting the output through a TQuery?
I've tried this in a TQuery (it works in SQLPlus)
variable x refcursor
exec Pr_GetPortfolios (:x)
but I get an invalid sql error.

The reason for needing this is that our applications all access the database
with non-data aware controls through a single TQuery descendant. Proc calls
are implemented as straight SQL exec strings, returning a single selected
dataset. So if these Oracle versions of the procs really can only be
accessed via a TSP we'll need to make a lot more structural changes rather
than just building in a different set of SQL resource strings.

Thanks again.

Andrew

Quote

"John Pierce" <jpie...@healthplanning.com> wrote in message

news:39455DE7.B219BB25@healthplanning.com...

Quote

> Andrew Mannering wrote:

> > Is it possible to use the TQuery for these or is does TStoredProc
support
> > this sort of thing? And how do you drive it anyway?

> > Any pointers gratefully appreciated.

> > Andrew Mannering
> > Sybase DBA

> You use a stored proc. Pick the refcursor as a parameter of type cursor,
in
> out.
> Then OPEN the SP and read the records in a loop just as you would do with
a
> tquery. Be sure to close the SP at the end.

> John Pierce

Wayne Menz

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Andrew Mannering wrote in <8i53te$q...@bornews.borland.com>:

Quote

>once I'd "discovered" the implicit commit feature of Oracle that made
>new rows invisible

Please explain.

--
Wayne Menzie

Scott Metzge

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

I would recommend using a Direct Oracle Access instead of the BDE it is made by
www.allroundautomations.nl

It will do what you want. Also, most people who are doing serious
Delphi/CBuilder development with Oracle are using this product.

Scott

Quote

Andrew Mannering wrote:
> Thanks for this - I can now see the output of the proc using the TStoredProc
> components (once I'd "discovered" the implicit commit feature of Oracle that
> made new rows invisible...)

> However, is there any way at all of getting the output through a TQuery?
> I've tried this in a TQuery (it works in SQLPlus)
> variable x refcursor
> exec Pr_GetPortfolios (:x)
> but I get an invalid sql error.

> The reason for needing this is that our applications all access the database
> with non-data aware controls through a single TQuery descendant. Proc calls
> are implemented as straight SQL exec strings, returning a single selected
> dataset. So if these Oracle versions of the procs really can only be
> accessed via a TSP we'll need to make a lot more structural changes rather
> than just building in a different set of SQL resource strings.

> Thanks again.

> Andrew

> "John Pierce" <jpie...@healthplanning.com> wrote in message
> news:39455DE7.B219BB25@healthplanning.com...

> > Andrew Mannering wrote:

> > > Is it possible to use the TQuery for these or is does TStoredProc
> support
> > > this sort of thing? And how do you drive it anyway?

> > > Any pointers gratefully appreciated.

> > > Andrew Mannering
> > > Sybase DBA

> > You use a stored proc. Pick the refcursor as a parameter of type cursor,
> in
> > out.
> > Then OPEN the SP and read the records in a loop just as you would do with
> a
> > tquery. Be sure to close the SP at the end.

> > John Pierce

--
------------------------------------------------------------
Yo Yo Dyne Technologies, Your source for D&D software
http://yoyodyne.virtualave.net/

John Pierc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Quote

Andrew Mannering wrote:
> Thanks for this - I can now see the output of the proc using the TStoredProc
> components (once I'd "discovered" the implicit commit feature of Oracle that
> made new rows invisible...)

> However, is there any way at all of getting the output through a TQuery?
> I've tried this in a TQuery (it works in SQLPlus)
> variable x refcursor
> exec Pr_GetPortfolios (:x)
> but I get an invalid sql error.

> The reason for needing this is that our applications all access the database
> with non-data aware controls through a single TQuery descendant. Proc calls
> are implemented as straight SQL exec strings, returning a single selected
> dataset. So if these Oracle versions of the procs really can only be
> accessed via a TSP we'll need to make a lot more structural changes rather
> than just building in a different set of SQL resource strings.

> Thanks again.

> Andrew

I haven't tested this, but the trick in a TQuery is to use an anonymous PL/SQL
block, thus:

begin
variable (etc);
procedurename(:x);
end;

Then use OPEN.

Note the ';' and the fact that you do not use Exec to call the procedure. You
may have to fool around with it a bit; I've never called an SP with a ref
cursor this way, but I've called lots of SPs this way.

I second the advice to use DOA. It performs flawlessly, compiles into the .exe,
and uses no .dlls.

John Pierce

Andrew Mannerin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Having come from Sybase background where transactions are only started on
request the idea of implicit transactions (i.e. everything is in a tx until
you say "commit"), I'd inserted a bunch of rows with SQLPlus, whcih I could
then select in SQLPlus, but them wasn't seeing them in anything else. Of
course, they were uncommited.

I vaguely remeber reading about this feature at some time in the past, but
it slipped my mind until someone else here commented about transactions...

Andrew Manering

Quote

"Wayne Menzie" <way...@bosmedtechdotcom.nospam> wrote in message

news:8F5269F6Ewayneshammalammading@207.105.83.65...

Quote

> Andrew Mannering wrote in <8i53te$q...@bornews.borland.com>:

> >once I'd "discovered" the implicit commit feature of Oracle that made
> >new rows invisible

> Please explain.

> --
> Wayne Menzie

Andrew Mannerin

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Putting this into the TQuery throws a wobbly - it thinks the ":cur" is a
local variable and seems to strip the ":" from the SQL. Turning off the
ParamCheck property makes no difference.
Putting the SQL string at runtime with ParamCheck off works a bit more - but
still fails with the marvellously informative "Could not find Object"
message....

begin
variable cur refcursor;
Pr_GetPortfolios(:cur);
end;

Does DOA plug into directly into Delphi TDatasets?

Andrew

Quote

"John Pierce" <jpie...@healthplanning.com> wrote in message

news:3946636F.4B8C2CF6@healthplanning.com...

Quote

> Andrew Mannering wrote:

> > Thanks for this - I can now see the output of the proc using the
TStoredProc
> > components (once I'd "discovered" the implicit commit feature of Oracle
that
> > made new rows invisible...)

> > However, is there any way at all of getting the output through a TQuery?
> > I've tried this in a TQuery (it works in SQLPlus)
> > variable x refcursor
> > exec Pr_GetPortfolios (:x)
> > but I get an invalid sql error.

> > The reason for needing this is that our applications all access the
database
> > with non-data aware controls through a single TQuery descendant. Proc
calls
> > are implemented as straight SQL exec strings, returning a single
selected
> > dataset. So if these Oracle versions of the procs really can only be
> > accessed via a TSP we'll need to make a lot more structural changes
rather
> > than just building in a different set of SQL resource strings.

> > Thanks again.

> > Andrew

> I haven't tested this, but the trick in a TQuery is to use an anonymous
PL/SQL
> block, thus:

> begin
> variable (etc);
> procedurename(:x);
> end;

> Then use OPEN.

> Note the ';' and the fact that you do not use Exec to call the procedure.
You
> may have to fool around with it a bit; I've never called an SP with a ref
> cursor this way, but I've called lots of SPs this way.

> I second the advice to use DOA. It performs flawlessly, compiles into the
.exe,
> and uses no .dlls.

> John Pierce

Wayne Menz

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Andrew Mannering wrote in <8ib12p$l...@bornews.borland.com>:

Quote

>Having come from Sybase background where transactions are only started
>on request the idea of implicit transactions (i.e. everything is in a tx
>until you say "commit"), I'd inserted a bunch of rows with SQLPlus,
>whcih I could then select in SQLPlus, but them wasn't seeing them in
>anything else. Of course, they were uncommited.

Oh, that makes much more sense. From your previous comment about "the
implicit commit feature" I thought you were experiencing commits that you
didn't explicitly initiate. That would be bad to not know when data was
committed. I believe that's why Oracle uses explicit transactions. It
took a few times for me to remember that whe switching from SQL Server 7
but now that I understand the whole process, it seems very intuitive.

--
Wayne Menzie

John Pierc

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Quote

Andrew Mannering wrote:
> Putting this into the TQuery throws a wobbly - it thinks the ":cur" is a
> local variable and seems to strip the ":" from the SQL. Turning off the
> ParamCheck property makes no difference.

> Putting the SQL string at runtime with ParamCheck off works a bit more - but
> still fails with the marvellously informative "Could not find Object"
> message....

> begin
> variable cur refcursor;
> Pr_GetPortfolios(:cur);
> end;

> Does DOA plug into directly into Delphi TDatasets?

Okay, I confess that I had never tried this before. The following works:

begin
JOHN.GET_MSMASTER_NAMES(:CUR, :VAL);
END;

In the parameters property of the Query, define CUR as cursor and give it a
value of 0. Define your other parameter and give it a value, then Open. This
could all be done at run-time with a TQuery.Create and dynamically created
parameters.

I tested this with D3 against O7.3.4 because my D5 is hooked up to DOA, but if
it works in 3, it'll work in 5.

TOracleDataSet is a descendent of TDataSet. As such it plugs into TDataSource,
and is a direct replacement for TQuery. There is also TOracleQuery, which is
extremely low-level and the thing to use for everything except data-aware
components.

John Pierce

Stephen Bertra

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Getting Result set from Oracle Stored procedure

Quote

John Pierce <jpie...@healthplanning.com> wrote in message

news:394950A4.CFBE2750@healthplanning.com...

Quote

> Okay, I confess that I had never tried this before. The following works:

> begin
> JOHN.GET_MSMASTER_NAMES(:CUR, :VAL);
> END;

> In the parameters property of the Query, define CUR as cursor and give it
a
> value of 0. Define your other parameter and give it a value, then Open.
This
> could all be done at run-time with a TQuery.Create and dynamically created
> parameters.

> I tested this with D3 against O7.3.4 because my D5 is hooked up to DOA,
but if
> it works in 3, it'll work in 5.

> John Pierce

Unfortunately it doesn't seen to work in D4, BDE 5.1.1.1 and O8.1.5 :-(

I can't access any stored procedure parameters via a TQuery, but they work
great with a TStoredProcedure, but of course I can't return a result set via
the latter - catch 22.5.

For example

staff_name is a stored procedure with 1 output varchar2 parameter

sp.StoredProcName := staff_name;
qry.sql.text := 'staff_name (:name)';

sp.ExecProd; // THIS WORKS
text := sp.parambyname('name').asstring;

qry.open; // FAILS WITH A
BDE ACCESS ERROR
text := sp.parambyname('name').asstring;

I'm sure there is simple workaround, but I haven't found it yet.

Cheers

Stephen

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT