Prev: 10.2.0.5 Patchset
Next: How to do path with spaces
From: Thomas Gagne on 19 Mar 2010 16:34
If inside a program I want to send multiple SQL statements inside a
single batch, how is this done inside a program?

I'm using .Net and OracleDataAdapter to submit an OracleCommand.
Whenever I try to send multiple select statements I get an error
complaining the SELECT needs an INTO clause, or if I try multiple
TRUNCATE statements I get an error complaining about a missing option.

I've tried using semicolons and forward slashes, but no luck.
From: Thomas Gagne on 19 Mar 2010 16:58
Can I use PL/SQL inside a .Net program? Does the same syntax work
inside SQLDeveloepr?

I'll go check it out. Thank you for the pointer.
From: John Hurley on 19 Mar 2010 17:55
On Mar 19, 4:34 pm, Thomas Gagne <tgga...(a)gmail.com> wrote:

snip

> If inside a program I want to send multiple SQL statements inside a
> single batch, how is this done inside a program?
>
> I'm using .Net and OracleDataAdapter to submit an OracleCommand.
> Whenever I try to send multiple select statements I get an error
> complaining the SELECT needs an INTO clause, or if I try multiple
> TRUNCATE statements I get an error complaining about a missing option.
>
> I've tried using semicolons and forward slashes, but no luck.

Do as much work as you can in a single SQL statement.

Multiple sql statements in a batch? Why would you want to do that in
oracle?
From: Thomas Gagne on 19 Mar 2010 22:40
John Hurley wrote:
> <snip>
>
> Multiple sql statements in a batch? Why would you want to do that in
> oracle?
>
Performance. Do as much with a single trip to the database as possible
to avoid going back and forth between client and server. Reduces
network traffic.
From: Mark D Powell on 20 Mar 2010 08:55
On Mar 19, 10:40 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote:
> John Hurley wrote:
> > <snip>
>
> > Multiple sql statements in a batch?  Why would you want to do that in
> > oracle?
>
> Performance.  Do as much with a single trip to the database as possible
> to avoid going back and forth between client and server.  Reduces
> network traffic.

Definitely. Besides using anonymous pl/sql from a program if
intermediate data from the SQL being submitted is not needed in the
program then it may be practical to code the logic into a database
stored procedure which can then be executed (called) by the client
program. The stored procedure can return a individaul data values or
a cursor if results are needed to the application.

Reducing round trips between the application and the database can have
significant performance impact. The pro* languages have long
supported array inserts, which are now available in pl/sql via bulk
collect opterations. Though there are times when you have to do
single row processing. If you move that processing into the database
via stored code you can still sometimes cut down on the round trips.

HTH -- Mark D Powell --




 |  Next  |  Last
Pages: 1 2
Prev: 10.2.0.5 Patchset
Next: How to do path with spaces