Geek asks and answers

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

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);

This is just an example, but suppose some of the fields were very large, and there were many statements, etc.

Edit: Putting statements in 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
]
+3
source share
2 answers

I'm not sure why @Tony Andrews answer is not working. (Perhaps some of the column names are causing problems, you can avoid the names COMMENT and TIMESTAMP. TIMESTAMP worked fine for me, but I had to change COMMENT to COMMENTS.) Perhaps inserting with multiple tables would 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
+1

, , PL/SQL :

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;
";
+3

More articles:


All Articles