CODEWITHSUNDEEP
c#oracle-database
sReas
sReas

Reputation: 21

"SELECT 1 FROM ... " in oracle returns a decimal type

I'm getting a datatable from sample query below in ORACLE "SELECT 1 AS X FROM ... "

when i select the X by using dr.Field("X"), it gives me error. I've found out that the it returns as decimal datatype.

Using the same statement in MSSQL has no problem, i've got the int datatype.

Any workaround or solution to this?

Upvotes: 1

Views: 2428

Answers (5)

Pedro Rainho
Pedro Rainho

Reputation: 4284

What I needed to do in order to get an int instead of a decimal was:

SELECT CAST(1 as NUMBER(9,0)) FROM Dual

Upvotes: 1

ddb
ddb

Reputation: 31

An integer in Oracle is a Number with precision 0. Since you are not specifying the size (can be larger than 16) it is cast to decimal.

Try casting it to number(16,0) and you should be ok.

For the people suggesting ODP.NET: it gives the same problem.

Upvotes: 3

Gonzalo
Gonzalo

Reputation: 21175

If you know the type of that column should be an int and assuming it's not null:

int x = Convert.ToInt32 (dr.Field ["X"]);

This will not have any effect with other DB providers that return an int for that column.

If you want to change your Oracle query to return an int instead:

SELECT CAST(1 AS INTEGER) FROM ...

Upvotes: 2

Cylon Cat
Cylon Cat

Reputation: 7201

I don't have my Oracle books here at home, but an explicit convert or cast to integer would probably be the way to go. (Speaking of the Oracle query, not your C# that retrieves the result, although you could convert there as well.) "1" is ambiguous, as far as Oracle is concerned.

Upvotes: 1

Donnie
Donnie

Reputation: 46923

Your front-end code is mis-autodetecting the type of the field. You should be able to override it to be an integer instead. Without more details on the language / etc being used, it's hard to be more specific.

Upvotes: 2

Related Questions