What is the OracleType of ROWNUM

Question

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?

Answer 1

I believe that OracleType.Number will work if the data type is the problem.

Answer 2

Good question. Have you tried: OracleType.UInt32 ? I'd try OracleType.Number as a last resort.

Answer 3

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)

Answer 4

Sorry everyone, the problem isn't the datatype. it's because the command object is used more than once in the method code to run different sql, with different parameters. Calling command.Parameters.Clear() after my call sorted the problem. Many thanks for the insight into how to find a DataType. It will be useful for further reference.

Answer 5

You need to use the OracleType.RowID for Oracle rowIDs. They are not numbers.

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracletype.aspx

This states:

The base64 string representation of an Oracle ROWID data type. Use the .NET Framework String or OracleClient OracleString data type in Value.