Re: How do oracle programmers send batches of SQL from inside a program?
Date: Sat, 20 Mar 2010 05:55:16 -0700 (PDT)
Message-ID: <3dc5105f-b3dd-48c3-922d-eee4ad369fc7_at_x12g2000yqx.googlegroups.com>
On Mar 19, 10:40 pm, Thomas Gagne <TandGandGA..._at_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 -- Received on Sat Mar 20 2010 - 07:55:16 CDT