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
]
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;
";
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
User contributions licensed under CC BY-SA 3.0