Oracle NUMBER problem: Decimal to Int64 cast

Question

We are developing a standalone application that stores its data in a Database. We use Dataset and TableAdapters for the communication with the Database. One of the basic requirements is the application to be able to use SQL Server, Oracle and MySQL. For this purpose we made the queries in the TAs vendor independent. We use ODBC provider.

It works fine for SQL Server and MySQL, but with Oracle we had problems. One of the problems we can not overcome is the casting error of decimal to Int64.

Oracle tables use NUMBER(38) for system IDs (autoincrement fields) that as it seems are mapped to decimal. However we use Int64/long in the DataSet/TAs since SQL Server and MySQL use BIGINT. (Note that the DataSEt TableAdapters where auto-generated from a SQL Server schema in the beginning.)

We get a cast exception when getting a NUMBER value from Oracle since it returns a decimal object.

DbCommand getIDcmd = connection.CreateCommand();
getIDcmd.CommandText = "select LAST_ID from SEQUENCE_ID";
sequenceID = (Int64)getIDcmd.ExecuteScalar(); // we get a cast exception here returns decimal

please note that we've tried to lower the precision number in Oracle (e.g. NUMBER(10)) as we've seen with other forum entries but with no luck.

Is this an ODBC problem? If we move to other provider will solve the problem?

Answer 1

Hi Markos,

If sequence_id is the name of your sequence your query doesn't look right in Oracle. You would query the last given ID with a query like:

SELECT last_number FROM all_sequences WHERE sequence_owner=? AND sequence_name=?

The cast shouldn't be a problem (if within int64 range).

Answer 2

You could run this to find out what type is actually returned, and then go from there:

System.Windows.Forms.MessageBox.Show(getIDcmd.ExecuteScalar().GetType().ToString());

Answer 3

It seems like this query will always return a `decimal'. You could try this :

sequenceID = Convert.ToInt64(getIDcmd.ExecuteScalar());

I do not think any loss of precision would occur in your case.

Note that lowering the precision of your NUMBER is always a good idea : Int64 maps to NUMBER(19). This will not solve your problem at hand, but it may prevent more problems in the future. For instance, if you get an IDataRecord including your id (using the Microsoft Oracle provider), a call to GetInt64 will fail when your column is defined as NUMBER(38) and succeed when defined as NUMBER(19) (even when when the value is in the correct range).