Hi there

I'm trying to pass this select statement to Oracle, using ADO
(specifically the TADOQuery object in Borland's compiler):

with T1 as (select train_i from train where train_i < 20) select * from
T1;

It fails saying the result does not return a dataset set.

It I do this:

select * train_i from train where train_i < 20;

it works fine.

Any clues? I need to be bypass the borland parsing, and although I
don't expect many here to know that compiler, perhaps a few clues could
help me?

Thanks

Dean

Create a view with the WITH statement and query it from your Borland
compiled application:

create view T1_vw as with T1 as (select train_i from train where
train_i < 20) select * from T1;

Cheers.

Well that is one idea, and a good one. But I would like to be able to
build up SQL strings in the code at will, and not create views (its a
big thing for us to get anything like a view created).

Thanks

Dean

"deanbrown3d" <deanbrown3d (AT) yahoo (DOT) com> wrote

Jim

deanbrown3d wrote:
I can't see the original post but one possibility for what you describe
is to write a stored procedure in the database capable of processing
anonymous blocks. I have one that will handle up to 4GB of PL/SQL.

Then pass in whatever transactions you want in the form of an anonymous
block and let the proc execute whatever comes in.

Of course SQL Injection is a concern if you do this so you must be very
careful with the front-end.
--
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

On Fri, 27 May 2005 06:02:16 -0700, deanbrown3d interested us by writing:

Strong suggestion: keep the 'build SQL strings at will' code style to a
minimum.

Most techniques for building strings dynamically are seriously
anti-scalable and often perform very poorly in an Oracle environment.
It's best to avoid the bulk of dynamic, redundantly hard-parsed SQL.

Read Tom Kyte's book 'Effective Oracle by Design' for a detailed
explanation, especially in the area of "bind variables".

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

Ok I had no idea about this, but I will look into it. Thanks for the
advice.

Dean