RETURNING INTO returns ORA-01036: illegal variable name/number

  • Thread starter Thread starter deast_98
  • Start date Start date
D

deast_98

Trying to return a value on INSERT using ASP.NET with
System.Data.OracleClient.

Errors on Execute statement. From other articles I have tried using
variable name like :p1 and also tried wrapping in anonymous block
BEGIN...;END;

Dim sSQL As String
Dim nOrderNumber As Integer

Dim cmd As New OracleCommand
If dwConn1.State = ConnectionState.Closed Then dwConn1.Open()
cmd.Connection = dwConn1

cmd.CommandText = "INSERT INTO Orders(user_id) VALUES('A12345')
RETURNING ID INTO ?"
cmd.CommandType = CommandType.Text

Dim p1 As New OracleParameter
p1.DbType = DbType.Int32
p1.Direction = ParameterDirection.Output
p1.ParameterName = "ID"
p1.SourceColumn = "ID"
cmd.Parameters.Add(p1)

cmd.ExecuteNonQuery()
nOrderNumber = CType(p1.Value, Integer)
 
Trying to return a value on INSERT using ASP.NET with
System.Data.OracleClient.

Errors on Execute statement. From other articles I have tried using
variable name like :p1 and also tried wrapping in anonymous block
BEGIN...;END;

Dim sSQL As String
Dim nOrderNumber As Integer

Dim cmd As New OracleCommand
If dwConn1.State = ConnectionState.Closed Then dwConn1.Open()
cmd.Connection = dwConn1

cmd.CommandText = "INSERT INTO Orders(user_id) VALUES('A12345')
RETURNING ID INTO ?"
cmd.CommandType = CommandType.Text

Dim p1 As New OracleParameter
p1.DbType = DbType.Int32
p1.Direction = ParameterDirection.Output
p1.ParameterName = "ID"
p1.SourceColumn = "ID"
cmd.Parameters.Add(p1)

cmd.ExecuteNonQuery()
nOrderNumber = CType(p1.Value, Integer)
Click to expand...

ID is a sequenced column?

FB
 
You definitely need to use the ":p1" parameter naming style. I was
able to use the RETURNING option in the INSERT INTO query using code that
appears at the end of this post.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.

'VB:
Dim strSQL As String
strSQL = "INSERT INTO MyTest (OtherCol) VALUES ('Test') " & _
" RETURNING ID INTO :ID"
Dim cmd As OracleCommand = cn.CreateCommand()
cmd.CommandText = strSQL
Dim p As OracleParameter = cmd.Parameters.Add(":ID", OracleType.Number)
p.Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Console.WriteLine(p.Value)

//C#:
string strSQL;
strSQL = "INSERT INTO MyTest (OtherCol) VALUES ('Test') " +
" RETURNING ID INTO :ID";
OracleCommand cmd = cn.CreateCommand();
cmd.CommandText = strSQL;
OracleParameter p = cmd.Parameters.Add(":ID", OracleType.Number);
p.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine(p.Value);
 
Back
Top