Execute Multiple SQL Script from .NET or ADO

From: <Muhanned>
Date: Sun, 02 May 2010 03:13:59 -0500
Message-ID: <20105241358muhanned.maayeh_at_globitel>

I put my script between Begin and End; PL/SQL block but, my code gives the following "ORA-00922: missing or invalid option". The scripts I am trying to execute is to create a schema and structures under the schema. The purpose of this is to develop an EXE that will accompany a CD package to install our products. So, has anyone tried this from .NET?

Mladen Gogala wrote:

That is done using PL/SQL.
19-Mar-10

That is done using PL/SQL. Essentially, you put a bunch of commands between "BEGIN" and "END" program delimiters.

......

Thomas, you should really get yourself a PL/SQL book by Steve Feuerstein. This book is not a replacement for reading manuals.

--
http://mgogala.byethost5.com

Previous Posts In This Thread:

On Friday, March 19, 2010 4:34 PM
Thomas Gagne wrote:

How do oracle programmers send batches of SQL from inside a program?
If inside a program I want to send multiple SQL statements inside a
single batch, how is this done inside a program?

I am 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 have tried using semicolons and forward slashes, but no luck.

On Friday, March 19, 2010 4:42 PM
Mladen Gogala wrote:

That is done using PL/SQL.
That is done using PL/SQL. Essentially, you put a bunch of commands
between "BEGIN" and "END" program delimiters.

......

Thomas, you should really get yourself a PL/SQL book by Steve Feuerstein.
This book is not a replacement for reading manuals.




--
http://mgogala.byethost5.com

On Friday, March 19, 2010 4:58 PM
Thomas Gagne wrote:

Can I use PL/SQL inside a .Net program?
Can I use PL/SQL inside a .Net program?  Does the same syntax work
inside SQLDeveloepr?

I will go check it out.  Thank you for the pointer.

On Friday, March 19, 2010 5:55 PM
John Hurley wrote:

snipDo as much work as you can in a single SQL statement.
snip


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?

On Friday, March 19, 2010 10:40 PM
Thomas Gagne wrote:

John Hurley wrote:Performance.
John Hurley wrote:
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.

On Saturday, March 20, 2010 8:55 AM
Mark D Powell wrote:

Definitely.
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 --

On Saturday, March 20, 2010 6:26 PM
Galen Boyer wrote:

I would test this theory.
I would test this theory.  In your OP, you said you were sending
multiple select statements.  I'd prove out that you are actually saving
anything when you send multiple statements at once.  Each statement
would be returning a resultset.  Where is the savings on the way out?

Then, once you see you are not saving anytime, you can then have your
client program execute a SQL statement exactly when it needs to.

--
Galen Boyer

On Sunday, March 21, 2010 7:31 AM
Thomas Gagne wrote:

This is a multi-part message in MIME format.
This is a multi-part message in MIME format.
--------------050304010400000909020208
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Galen Boyer wrote:
I have already done that and the savings are dramatic.

Listen, if Oracle cannot do it that is OK.  I will figure out how best to do
it with what Oracle does provide and move on to the next problem.

--------------050304010400000909020208
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit



<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">

</head>

<body bgcolor="#ffffff" text="#000000">

Galen Boyer wrote:


<blockquote cite="mid:uzl22fx42.fsf_at_www.yahoo.com" type="cite">&lt;snip&gt;

<pre wrap=""><!---->

I would test this theory.  In your OP, you said you were sending
multiple select statements.  I'd prove out that you are actually saving
anything when you send multiple statements at once.  Each statement
would be returning a resultset.  Where is the savings on the way out?



</pre>

</blockquote>

I have already done that and the savings are dramatic.<br>


<br>

Listen, if Oracle cannot do it that is OK.&nbsp; I will figure out how best to
do it with what Oracle does provide and move on to the next problem.<br>


</body>

</html>


--------------050304010400000909020208--

On Sunday, March 21, 2010 12:10 PM
John Hurley wrote:

snipoFrom the questions you are asking it really sounds to me as if youneed to
snip

o

From the questions you are asking it really sounds to me as if you
need to spend a bunch of time learning Oracle and how to do things
effectively and efficiently.

The learning curve is fairly steep and you want to throw out anything
that you think you already know about how to do it in other systems
first.

On Wednesday, March 24, 2010 10:05 PM
Galen Boyer wrote:

What savings?
What savings?  I do not see how you can save hardly anything by sending 3
sql statements together to the database and returning 3 result sets, vs
sending 1 sql statement and recieving 1 result set 3 times in a row.

Can you give a simple "select fld from t1" example and show these numbers?


Oracle can do this.  An anonymous block of code with each sql statement
would suffice.

--
Galen Boyer


Submitted via EggHeadCafe - Software Developer Portal of Choice 
BizTalk: Incorporating conditional If / Else Functoid Logic in a map.
http://www.eggheadcafe.com/tutorials/aspnet/f6fc20ab-5c6a-4f04-8a0b-bba39e4bbcf0/biztalk-incorporating-co.aspx

Received on Sun May 02 2010 - 03:13:59 CDT