How can I run several different `insert` statements in a single call with ADO.NET and Oracle?

3

The following works with MS SQL Server, but our Oracle system throws an error: ORA-00911: invalid character

string strQuery = @"

insert into DEVICE
( DEVICE_ID, DEVICE_NAME, TIMESTAMP) values
(:DEVICE_ID,:DEVICE_NAME, sysdate);
insert into INV
( INV_ID, DEVICE_ID, COMMENT, TIMESTAMP) values
(:INV_ID,:DEVICE_ID,:COMMENT, sysdate);

";

OracleConnection conn = getConn();
OracleCommand cmd = new OracleCommand(strQuery, conn);
cmd.Parameters.AddWithValue(":DEVICE_ID", device.Id);
cmd.Parameters.AddWithValue(":DEVICE_NAME", device.Name);
cmd.Parameters.AddWithValue(":INV_ID", newInvId());
cmd.Parameters.AddWithValue(":COMMENT", device.Comment);

It's only an example, but assume, that some of the fields were very large and there was a ton of statements etc.

Edit: Putting the statements into a block (as in Tony Andrews answer) results in the following error:

[OracleException (0x80131938): ORA-06550: line 1, column 1:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin case declare exit for function goto if loop mod null
   package pragma procedure raise return select separate type
   update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   form table call close current define delete fetch lock insert
   open rollback savepoint set sql execute commit forall merge
   library OPERATOR_ pipe
]
.net
sql
oracle
batch-file
asked on Stack Overflow Jun 3, 2011 by matthias.lukaszek • edited Jun 3, 2011 by matthias.lukaszek

2 Answers

3

I'm not sure, but would it work if you turned it into a PL/SQL anonymous block like this?:

string strQuery = @"
begin
  insert into DEVICE
    ( DEVICE_ID, DEVICE_NAME) values
    (:DEVICE_ID,:DEVICE_NAME);
  insert into INV
    ( INV_ID, DEVICE_ID, COMMENT) values
    (:INV_ID,:DEVICE_ID,:COMMENT);
end;
";
answered on Stack Overflow Jun 3, 2011 by Tony Andrews
1

I'm not sure why @Tony Andrews answer didn't work. (Perhaps some of the column names are causing problems; you may want to avoid the names COMMENT and TIMESTAMP. TIMESTAMP worked ok for me, but I had to change COMMENT to COMMENTS.) Maybe a multi-table insert will work better:

insert all
    into device(device_id, device_name, timestamp)
    values(device_id, device_name, the_date)
    into inv(inv_id, device_id, comments, timestamp)
    values(inv_id, device_id, comments, the_date)
select :device_id device_id, :device_name device_name
    ,sysdate the_date, :inv_id inv_id, :comments comments from dual
answered on Stack Overflow Jun 5, 2011 by Jon Heller

User contributions licensed under CC BY-SA 3.0