How to write oracle insert query for return value in c#?

Question

I wrote query which I got no return value as follows:

  con.Open();
  OracleCommand  cmd= CreateCommand();
  cmd.CommandText = $"INSERT INTO {"Customer".GetDoubleQuoted()}({"City".GetDoubleQuoted()},{"CompanyName".GetDoubleQuoted()} , {"ContactName".GetDoubleQuoted()} ,{"Country".GetDoubleQuoted()},{"CustomerID".GetDoubleQuoted()},{"Phone".GetDoubleQuoted()}) VALUES ('Chicago', 'Amisys','Jwk', 'USA','aaa', '123456') \n Returning {"City".GetDoubleQuoted()} into :city";
  cmd.Connection = con;
  cmd.Parameters.Add(new OracleParameter
  {
       ParameterName = ":city",
       OracleDbType = OracleDbType.Varchar2,
       Direction = ParameterDirection.Output
   });
   cmd.ExecuteNonQuery();
  var value= cmd.Parameters[":city"].Value.ToString();

Could someone help me finding out why value doesn't come out?

Thanks in advance. joon

Try setting the ParameterDirection to ReturnValue

Roy Sanchez
– Roy Sanchez

2018-01-29 18:45:38 +00:00
Commented Jan 29, 2018 at 18:45 — Roy Sanchez
– Roy Sanchez, Commented Jan 29, 2018 at 18:45
Thanks Roy, but it doesn't make any change at all.

Joon w K
– Joon w K

2018-02-02 01:26:06 +00:00
Commented Feb 2, 2018 at 1:26 — Joon w K
– Joon w K, Commented Feb 2, 2018 at 1:26

Roy Sanchez · Accepted Answer · 2018-02-06 19:02:49Z

1

The error is a small gotcha in ODP.NET, everytime you receive a varchar2 returnValue parameter you must specify the size. So simply adding the line Size=32000, to your example will make it work.

See the full code bellow:

 con.Open();
 OracleCommand  cmd= CreateCommand();
 cmd.CommandText = $@"INSERT INTO {"Customer".GetDoubleQuoted()(
        {"City".GetDoubleQuoted()},
        {"CompanyName".GetDoubleQuoted()},
        {"ContactName".GetDoubleQuoted()},
        {"Country".GetDoubleQuoted()},
        {"CustomerID".GetDoubleQuoted()},
        {"Phone".GetDoubleQuoted()}
     ) VALUES ('Chicago', 'Amisys','Jwk', 'USA','aaa', '123456')
     Returning {"City".GetDoubleQuoted()} into :city";
 cmd.Connection = con;
 cmd.Parameters.Add(new OracleParameter
     {
         ParameterName = ":city",
         OracleDbType = OracleDbType.Varchar2,
         Size = 32000,
         Direction = ParameterDirection.ReturnValue
     });
 cmd.ExecuteNonQuery();
 var value= cmd.Parameters[":city"].Value.ToString();

Note: 32000 is the maximum size for a varchar2 column.

answered Feb 6, 2018 at 19:02

Roy Sanchez

8546 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joon w K Over a year ago

Thanks Roy, I got return value as expected. Thanks again.

Collectives™ on Stack Overflow

How to write oracle insert query for return value in c#?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related