OracleParameter for Dynamic SQL doesn't get set

[Guest]

I get this error when trying to use a paramter on a dynamic sql statement.
What am I doing wrong.

Exception Details: System.Data.OracleClient.OracleException: ORA-01036:
illegal variable name/number

Here is the code:

Dim strSQL As String
Dim intMyVal as integar = 5
strSQL = "SELECT * FROM TABLE WHERE(NUMBERCOL = @VAL)"

Dim oOracleConn As New OracleConnection(connstring)
If oOracleConn.State = ConnectionState.Closed Then
oOracleConn.Open()
End If

Dim cmdRoles As New OracleCommand(strSQL, oOracleConn)
cmdRoles.CommandType = CommandType.Text

Dim prm1 As New OracleParameter("@VAL", OracleType.Number)
prm1.Size = 3
prm1.Scale = 0
prm1.Value = intMyVal
prm1.Direction = ParameterDirection.Input
prm1.SourceVersion = DataRowVersion.Current
cmdRoles.Parameters.Add(prm1)

Dim dsNames As New DataSet
Dim da As New OracleDataAdapter(cmdRoles)
da.Fill(dsRoles) <-- fails here. when executing the command.

I do not how to make this work. The oracle column is NUMBER SIZE 3, SCALE 0.
Any help would be appreciated.

 

[Ben Lucas]

The Oracle Data Provider works a little bit differently than the SQL Server
data provider.

Your parameter name should begin with a : instead of an @
Then when you add the parameter, don't use the : in front of it for the
parameter name.

So your code would look like:

Dim strSQL As String
Dim intMyVal as integar = 5
strSQL = "SELECT * FROM TABLE WHERE(NUMBERCOL = :VAL)"

Dim oOracleConn As New OracleConnection(connstring)
If oOracleConn.State = ConnectionState.Closed Then
oOracleConn.Open()
End If

Dim cmdRoles As New OracleCommand(strSQL, oOracleConn)
cmdRoles.CommandType = CommandType.Text

Dim prm1 As New OracleParameter("VAL", OracleType.Number)
prm1.Size = 3
prm1.Scale = 0
prm1.Value = intMyVal
prm1.Direction = ParameterDirection.Input
prm1.SourceVersion = DataRowVersion.Current
cmdRoles.Parameters.Add(prm1)

Dim dsNames As New DataSet
Dim da As New OracleDataAdapter(cmdRoles)
da.Fill(dsRoles) <-- fails here. when executing the command.

 

[Guest]

OracleParameter.ParameterName must comply with Oracle "bind variable" syntax
rules: 1) lexical requirement is-- starts with a letter and then can be
letter, numbers, $, _, or # up to length 30 and 2) when used in query syntax
is preceded by a colon)

so, query should be "select from table where numcol = :val"
and then construction and filling of parameter should be:

Dim prm1 As New OracleParamet("val", OracleType.Int32)
prm1.Value = intMyVal
cmdRoles.Parameters.Add(prm1)


note: it doesn't seem to make any sense to use a databinding property on a
parameter whose value is supplied programaticaly. Also, you can save
yourself trouble by using the OracleType that is most directly related to
your actual program variable type (integer or System.Int32). The size of the
Oracle data column is of no importance in this case since you are just
comparing an integer value to ones found within the column, although you know
nothing in the column can exceed 999. But OracleType.Number actually
corresonds most closely to System.Decimal, not System.Int32. In any event,
type checking and/or type consistency will never substitute for bounds
checking.

 

[Guest]

I get this error when trying to use a paramter on a dynamic sql statement.
What am I doing wrong.

Exception Details: System.Data.OracleClient.OracleException: ORA-01036:
illegal variable name/number

Here is the code:

Dim strSQL As String
Dim intMyVal as integar = 5
strSQL = "SELECT * FROM TABLE WHERE(NUMBERCOL = @VAL)"

Dim oOracleConn As New OracleConnection(connstring)
If oOracleConn.State = ConnectionState.Closed Then
oOracleConn.Open()
End If

Dim cmdRoles As New OracleCommand(strSQL, oOracleConn)
cmdRoles.CommandType = CommandType.Text

Dim prm1 As New OracleParameter("@VAL", OracleType.Number)
prm1.Size = 3
prm1.Scale = 0
prm1.Value = intMyVal
prm1.Direction = ParameterDirection.Input
prm1.SourceVersion = DataRowVersion.Current
cmdRoles.Parameters.Add(prm1)

Dim dsNames As New DataSet
Dim da As New OracleDataAdapter(cmdRoles)
da.Fill(dsRoles) <-- fails here. when executing the command.

I do not how to make this work. The oracle column is NUMBER SIZE 3, SCALE 0.
Any help would be appreciated.

User submitted from AEWNET (http://www.aewnet.com/)

 

[Guest]

Hi Mike:

Change ":" instead of "@"

Remember that Oracle use the ":" symbol to identify parameters into the SQL parametrized statements.

I hope this helps

I get this error when trying to use a paramter on a dynamic sql statement.
What am I doing wrong.

Exception Details: System.Data.OracleClient.OracleException: ORA-01036:
illegal variable name/number

Here is the code:

Dim strSQL As String
Dim intMyVal as integar = 5
strSQL = "SELECT * FROM TABLE WHERE(NUMBERCOL = @VAL)"

Dim oOracleConn As New OracleConnection(connstring)
If oOracleConn.State = ConnectionState.Closed Then
oOracleConn.Open()
End If

Dim cmdRoles As New OracleCommand(strSQL, oOracleConn)
cmdRoles.CommandType = CommandType.Text

Dim prm1 As New OracleParameter("@VAL", OracleType.Number)
prm1.Size = 3
prm1.Scale = 0
prm1.Value = intMyVal
prm1.Direction = ParameterDirection.Input
prm1.SourceVersion = DataRowVersion.Current
cmdRoles.Parameters.Add(prm1)

Dim dsNames As New DataSet
Dim da As New OracleDataAdapter(cmdRoles)
da.Fill(dsRoles) <-- fails here. when executing the command.

I do not how to make this work. The oracle column is NUMBER SIZE 3, SCALE 0.
Any help would be appreciated.

User submitted from AEWNET (http://www.aewnet.com/)