Getting ORA-01036 error when using output parameter in C#

2
votes

I am having a problem with an Output parameter in C#/Oracle. I have isolated the code that I need to get working.

This is part of a much larger SQL statement, so do not worry too much if it doesn't make sense. In short I need to copy a row, give it a new ID and return that new ID. I tried using "RETURNING" which did not work. I see no reason why the code below should not work, but I'm getting an "ORA-01036: illegal variable name/number" error. Can anyone see what I'm doing wrong?

using (OracleConnection conn = new OracleConnection(connString))
{
    // Open connection and create command.
    conn.Open();
    using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("outValue", OracleType.Int32).Direction = ParameterDirection.Output;
        cmd.CommandText = "SELECT seq.nextval INTO :outValue FROM dual";
         try
         {
             cmd.ExecuteNonQuery();
         }
         catch (Exception e)
         {
             // This is just to see the exception when it fails.
         }
     }
 }
3
c#oracle
"c#" doesn't give that error... an oracle library, or the oracle server gives that error... - Marc Gravell
Yes, obviously it is the Oracle Library giving me the error. I write it like that because the SQL statement works fine in PL/SQL. - Novac
Changed the title to avoid misunderstandings. - Novac

3 Answers

0
votes

You are using named parameters. Try setting:

cmd.BindByName = true;
0
votes

The name of the parameter doesn't match.

cmd.Parameters.Add(":outValue", OracleType.Int32).Direction.......;
                    ^

I have also seen this variation on the query syntax

"BEGIN SELECT seq.nextval INTO :outValue FROM dual END;"
0
votes

Have you tried 'returning' keyword like this? This code works for me.

 using (OracleConnection conn = new OracleConnection(connString))
 {
     // Open connection and create command.
     conn.Open();
     using (OracleCommand cmd = new OracleCommand())
     {
         cmd.Connection = conn;
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add("outValue", OracleType.Int32).Direction = ParameterDirection.Output;
         cmd.CommandText = "insert into table (id, value) values (seq.nextval, 'value') returning id into :outValue";
         cmd.ExecuteNonQuery();
     }
 }