How to get string value from an OracleParameter object when using ExecuteNonQuery

Question

As the code Shows below, I want to insert a row into a database table (oracle 11) and return a String-Value of the inserted row.

using (OracleCommand cmd = con.CreateCommand()) {
    cmd.CommandText = "insert into foo values('foo','bar') returning idString into :lastIdParam";
    cmd.Parameters.Add(new OracleParameter("lastIdParam", OracleDbType.Varchar2), ParameterDirection.ReturnValue);
    cmd.ExecuteNonQuery();  // returning 1 (insert row successfully)

    var result = cmd.Parameters["lastIdParam"].Value.ToString();   // == String.Empty
}

Debugging shows that lastIdParam.Value's value = Empty.String: enter image description here

My Problem is, that I'm not getting the return string into my return-parameter but it will work when returning an integer value (like sequence no of inserted id). Cast Problem? ...?

The idString is filled if running the Statement directly (or if I just do something like returning 'ABC' into :myOutputParameter

Any ideas how to retrieve a string after inserting row?

What does not working mean? Doesn't it compile? Doesn't it return anything? Does it return a wrong value? Does it throw an exception? Please provide an exact description including the full error message if applicable. — Codo
– Codo, Commented Dec 21, 2012 at 15:50
@JWiley Oracle.DataAccess.Client.ExecuteNonEquery.OracleCommand.ExecuteNonQuery is returning an int: Signature = public override int ExecuteNonQuery(); — road242
– road242, Commented Dec 21, 2012 at 15:53
@Codo The screenshot above shows the debug result. So it is compiling and the return value is always an empty string — road242
– road242, Commented Dec 21, 2012 at 15:54
Have you tried setting a size for the parameter? Like this: new OracleParameter("lastIdParam", OracleDbType.Varchar2, 128) — Codo
– Codo, Commented Dec 21, 2012 at 16:08

Codo · Accepted Answer · 2012-12-21 16:18:51Z

3

Have you tried setting a size for the parameter? The default size is 0.

new OracleParameter("lastIdParam", OracleDbType.Varchar2, 128)

answered Dec 21, 2012 at 16:18

Codo

79.7k18 gold badges176 silver badges218 bronze badges

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

Comments

Alex Filipovici · Accepted Answer · 2012-12-21 16:22:07Z

0

The idString is an expression which has no value in your context, unless it is a column name in your table. Therefore, it is epected to be empty. You may change your query like the example below and see what happens.

cmd.CommandText = "insert into foo values('foo','bar') returning hereYouHaveToUseAColumnFromTheFooTable into :lastIdParam";

answered Dec 21, 2012 at 16:22

Alex Filipovici

32.8k7 gold badges60 silver badges81 bronze badges

1 Comment

road242 Over a year ago

Yes, it's importand that idString is the name of a column otherwise you will get an error (ORA-06550).

Collectives™ on Stack Overflow

How to get string value from an OracleParameter object when using ExecuteNonQuery

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related