I am trying to parameterise all our existing sql, but the following code is giving me a problem:
command.CommandText = String.Format("SELECT * FROM({0})
WHERE ROWNUM <= :maxRecords", command.CommandText);
command.Parameters
.Add("maxRecords", OracleType.Int32).Value = maxRecords;
The error reported is "ORA-01036: illegal variable name/number".
I assume that this is because OracleType.Int32 is not the correct datatype for ROWNUM. So what is it?
In SQLPlus we can create a view that includes rownum
to see exactly what datatype Oracle uses:
TEST>create view v_test as select rownum rn, dummy from dual;
View created.
TEST>desc v_test
Name Null? Type
-------- -------- -------------
RN NUMBER
DUMMY VARCHAR2(1)
So to Oracle, this pseudocolumn is a Number. Which based on this link ( http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i16209 ) can hold:
The following numbers can be stored in a NUMBER column:
-Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits
-Negative numbers from -1 x 10^-130 to 9.99...99 x 10^125 with up to 38 significant digits
-Zero
-Positive and negative infinity (generated only by importing from an Oracle Database, Version 5)
I believe that OracleType.Number
will work if the data type is the problem.
Good question. Have you tried: OracleType.UInt32 ? I'd try OracleType.Number as a last resort.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With