Multiple INSERTs to Oracle in a single command - how to?

[Tomasz J]

Hello Developers,

I just cannot figure this out. How to separate multiple Oracle INSERT
commands, so my inserts execute as a single batch. Semicolon does not seem
to work - Oracle returns: "ORA-00911: invalid character".

Could anyone advise?

Tomasz J

 

[Guest]

Tomasz,

try to wrap your statements in begin .. end block as well as separating them
with semicolons:

begin
insert 1 sql;
insert 2 sql;
end

 

[WenYuan Wang [MSFT]]

Hello Tomasz,

I have reproduced this issue. We need to perform more research on this
issue.
I will reply here as soon as possible.
If you have any more concerns on it, please feel free to post here.


Thanks for your understanding!
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Tomasz,
Thanks for your waiting.

http://support.microsoft.com/kb/q271494/
[How To Send Batch SQL Statements to Oracle]

Just as what Sergey said, wrapping the statement with begin. End block and
putting them in single line will achieve what you need.

I have tried with following line. (works fine)

System.Data.OracleClient.OracleConnection oc = new
System.Data.OracleClient.OracleConnection(...);
System.Data.OracleClient.OracleCommand ocd = new
System.Data.OracleClient.OracleCommand();
ocd.Connection=oc;
ocd.CommandText = @"begin insert into Task values(11,'tname');insert into
Task values(12,'tname');insert into Task values(13,'tname');insert into
Task values(14,'tname');end;";
oc.Open();
int flag=ocd.ExecuteNonQuery();
oc.Close();

Hope this helps. Let me know if you have any more concern. I'm glad to
assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tomasz J]

Thank you! That helped

TJ

 

[Tomasz J]

Thanks!

Do you by a chance also know how to group/separate DROP TABLE statements?

The similar approach does not work.

Tomasz J

 

[WenYuan Wang [MSFT]]

Hello Tomasz,

It seems DDL(Data Definition Language) is not allowed in PL/SQL block, in
Oracle world.

I tried the following PL/SQL command on Oracle database.
begin
drop table t1;
drop table t2;
end;

ORA-06550: line 2, column 1;
PLS-00103: Encountered the symbol "DROP" when expecting one of the
following:
Begin case delare exit for goto if loop mod null pragma raise return select
update while with <an identifier> <a double-quoted delimited-identifier> <a
bind variable> << close current delete fetch lock insert open rollback
savepoint set sql execute commit for all merge pipe.

Then, I downloaded ODP.Net (Oracle Data Provider for .Net) and tried. I
received the same error message.

At last, I searched this issue on internet, found the following document.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic
_sql.htm#BJEHDACE
============================================================================
==============
Executing DDL and SCL Statements in PL/SQL
Only dynamic SQL can execute the following types of statements within
PL/SQL program units:
" Data definition language (DDL) statements such as CREATE, DROP, GRANT,
and REVOKE
" Session control language (SCL) statements such as ALTER SESSION and SET
ROLE
" The TABLE clause in the SELECT statement
============================================================================
==============

Please try the following code and it works on my side. Hope this help.

ocd.CommandText = @"begin EXECUTE IMMEDIATE 'drop table t1';EXECUTE
IMMEDIATE 'drop table t2';end;";

Please let me know if you have any more concern. I'm glad to assist you.
Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Tomasz,

This is Wen Yuan again. Have you tried with Dynamic SQL? Does it work on
your side?
I just want to check if you have resolved the issue so far.
Please let me know if there is anything we can help with. I'm glad to
assist you.

Have a great day,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tomasz J]

Thanks much - it worked!

TJ

 

[Vasini]

Program too Large Error in Oracle with Mulitple Inserts

Hello,

I know this discussion was long before. But I have the same problem. I have 4 related tables. I have to insert data to all the tables with by insert scripts. My script has almost 400 to 500 insert statements for each table. I have to execute all the statements together. I am getting the Error

PLS-00123: program too large
PL/SQL: ORA-06541: PL/SQL: compilation error - compilation aborted
PL/SQL: SQL Statement ignored

Can anyone please help me on this ?

Thanks.