insert statement returning id

insert statement returning id

Postby curelom2 » Wed 22 Jan 2014 17:53

I haven't been able to find any good examples (only partial examples at best) of how to insert with a returning statement in Oracle. I've tried the following only to get an error. My table has a trigger on insert to populate the primary key "id"

Code: Select all
using (OracleConnection conn = new OracleConnection(@"User Id=my_user;Password=my_pw;Server=prod-db;Direct=True;Sid=CORP01;Port=1526;Persist Security Info=True")) {               
conn.Direct = true;
await conn.OpenAsync();

var sql = @"
         insert into my_table ( SCODE ,PCODE )
         values (:col1, :col2 ) returning id into :output_id";

OracleParameter outputParam = new OracleParameter()
{
     ParameterName = ":output_id",
     Direction = ParameterDirection.ReturnValue
};
OracleParameter[] parameters = new OracleParameter[]
{     
     new OracleParameter(":col1", OracleDbType.VarChar,  v.SCODE, ParameterDirection.Input),
     new OracleParameter(":col2", OracleDbType.VarChar,  v.PCODE, ParameterDirection.Input),
     outputParam
};


     var cmd = new OracleCommand(sql, conn);
     cmd.Parameters.AddRange(parameters);


     var vId = await cmd.ExecuteNonQueryAsync();
}

The error I get is:
PLS-00103: Encountered the symbol "" when expecting one of the following:



begin case declare 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 forall merge pipe

The symbol "" was ignored.

ORA-06550: line 2, column 65:

PLS-00103: Encountered the symbol "" when expecting one of the following:



( select values

The symbol "" was ignored.
curelom2
 
Posts: 1
Joined: Wed 22 Jan 2014 17:40

Re: insert statement returning id

Postby Pinturiccio » Thu 23 Jan 2014 13:56

We have reproduced the issue with Oracle 10g database. We will investigate it and notify you about the results as soon as possible.

As a temporary workaround, use OCI connection mode with Oracle 10g or Direct connection mode with Oracle 11g.
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: insert statement returning id

Postby Pinturiccio » Mon 17 Feb 2014 14:12

We have fixed the bug with INSERT, UPDATE or DELETE statements, which contain returning clause and '\r\n' symbols, in the Direct mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: insert statement returning id

Postby Pinturiccio » Tue 18 Feb 2014 16:25

New build of dotConnect for Oracle 8.2.103 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=28967
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle