0

I am getting error when I update a number and try to get back that number. I have already googled for this error but all codes suggest the same solution I have but still I get this error.

Oracle.DataAccess.Client.OracleException ORA-01036: illegal variable
name/number at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

Here is my C# code:

//add one to sequence and update it
query = "update unique_sequences " +
    "set last_sequence_number=last_sequence_number + 1 " + 
    "where unique_sequence_table_name='person' " +
    "returning last_sequence_number into :outnumber";
command.CommandText = query;
OracleParameter last_number = new OracleParameter("outnumber", OracleDbType.Varchar2, 13);
last_number.Direction = ParameterDirection.Output;

command.Parameters.Add(last_number);
command.ExecuteNonQuery();

where last_sequence_number is VARCHAR2(13 BYTE).

Thank you in advance.

riz
  • 69
  • 1
  • 7

1 Answers1

0

You need to use different ParameterDirection Use:

last_number.Direction = ParameterDirection.ReturnValue;
Habib
  • 219,104
  • 29
  • 407
  • 436
  • Thanks Habib. I tried your solution but the page is stuck on command.ExecuteNonQuery(); I added a breakpoint as well but no Locals show up. Any suggestion? – riz Feb 26 '15 at 15:15
  • So `ExectueNonQuery` is taking time ? or are you getting any exception ? – Habib Feb 26 '15 at 15:44
  • Yes `ExecuteNonQuery` is taking time and no exception. – riz Feb 26 '15 at 16:37
  • @user1424629, what are you trying to achieve in `set last_sequence_number=last_sequence_number` ? – Habib Feb 26 '15 at 16:38
  • Sorry, query was not complete. I am incrementing the number by one and want to return that number after increment. – riz Feb 26 '15 at 16:47
  • @user1424629, have you tried executing the query directly against the DB ? – Habib Feb 26 '15 at 16:47