I have an application using a TQuery on the Oracle DB; however it takes way too long to execute.  I read that a stored procedure should
optimize the Query by reducing network traffic and having the SQL code pre-compiled, but from what I can gather, Oracle stored
procedures can not return a result set with more than one row.  If this is true, then what good is Oracle good for anyway.

row.
No, Oracle SPs do not return result sets. We have several server side
utilities that emulate (simulate) result set from a store procedure.

Oracle DBMS is probably one of the most reliable backends SQL servers that
you can have. IT JUST DOES NOT CRASH!!!. We have several "Wall Street"
Clients running million row tables (managing billion dollar accounts)
without glitches, I wish I could say the same about other SQL servers...
Together, Oracle and Delphi, are one of the most potent combinations for
mission critical apps (front end and back end process).

----------------------------------------------
Paul Jaime
ITCG, Inc.
Madison NJ, USA

pja...@interactive.net
---------------------------------------------

DJenn...@XMission.com wrote in article <5arpm7$...@news.xmission.com>...

way too long to execute.  I read that a stored procedure should
pre-compiled, but from what I can gather, Oracle stored
is true, then what good is Oracle good for anyway.

You need to clarify what you mean by using stored procedures to reduce
network traffic...

As for result sets: I have never yet been in a position to where I could
not solve things with result sets by writing PL/SQL-functions that then
integrate directly into queries. (Now, THAT can reduce traffic!)

As for speed: If it runs slowly, stored procedures probably won't solve it.
More likely it's your select statement that needs tuning, or some vital
indexes are missing, or whatever

There are also ways to 'tell' the optimizer how to operate, in addition to
store precompiled SQL-statements!

best regards
 Kri Poulsen

BTW: I'm NOT paid by Oracle to say this. It just came out. In fact, our
main installation has crashed (BANG) 3 times since Friday..

I think Oracle 7.3 provides this functionality of returning result sets
from Stored Procedures. Barring unexpected problems Delphi 3.0 will
support this.

Vijay
R&D Manager, DB Engine and Connectivity

Oracle is VERY good for all the rest, in essence being a RDBMS! It is
possible to write PL/SQL code retrieving whole result sets, meaning
more than one row, but the bottleneck is: how do I get them to my app.
I never succeeded in that, nor could any of Oracle's consultants help
me with that. I'm expecting that there will be some day that a future
release will provide that, as Interbase already has it a long time
now...

Meanwhile, if you just want a stored proc to get a result set from a
comlex parameterized query, most of the time you could get the same
from defining a view at the server, then select from that...

Good luck!

Jasper