Executing SQL via MDAC ODBC multiple statements?

[Richard Adams]

Is it possible to execute more than one statement in SQL via MDAC
ODBC? I have a fairly complex select I wanted to create a view with,
but trying to send it all as one string with terminators ';' between
doesn't work (invalid character.) How can I do this kind of thing
from VB.NET?

Thanks in advance
Richard

[Justin Cave]

ac*****@concentric.net (Richard Adams) wrote in message news:<c2*************************@posting.google.c om>...

Is it possible to execute more than one statement in SQL via MDAC
ODBC? I have a fairly complex select I wanted to create a view with,
but trying to send it all as one string with terminators ';' between
doesn't work (invalid character.) How can I do this kind of thing
from VB.NET?

You can only execute one PL/SQL block at a time in Oracle. You may be
able to combine a number of statements into a single anonymous PL/SQL
block, though-- i.e.

declare
<<list of variables>>
begin
SQL Statement 1;
SQL Statement 2;
SQL Statement 3;
end;

When you start wanting to do this sort of thing from a client
application, however, I would strongly recommend writing a stored
procedure that does whatever you want to do and calling that stored
procedure from the client.

Justin Cave
Distributed Database Consulting, Inc.
www.ddbcinc.com/askDDBC

[FaheemRao]

What I understand from you question is that you want to send multiple
SQL statement at a time.

What my question is that why you want to send all sqls in same call ,
you can rather make multiple calls to database.

Please explain in more detailed that why you need to execute all sqls
in same vb.net API call.
Faheem

jc***@ddbcinc.com (Justin Cave) wrote in message news:<c8**************************@posting.google. com>...

ac*****@concentric.net (Richard Adams) wrote in message news:<c2*************************@posting.google.c om>...

Is it possible to execute more than one statement in SQL via MDAC
ODBC? I have a fairly complex select I wanted to create a view with,
but trying to send it all as one string with terminators ';' between
doesn't work (invalid character.) How can I do this kind of thing
from VB.NET?

You can only execute one PL/SQL block at a time in Oracle. You may be
able to combine a number of statements into a single anonymous PL/SQL
block, though-- i.e.

declare
<<list of variables>>
begin
SQL Statement 1;
SQL Statement 2;
SQL Statement 3;
end;

When you start wanting to do this sort of thing from a client
application, however, I would strongly recommend writing a stored
procedure that does whatever you want to do and calling that stored
procedure from the client.

Justin Cave
Distributed Database Consulting, Inc.
www.ddbcinc.com/askDDBC