Problem using Oracle NUMBER datatype in ADO 
Author Message
 Problem using Oracle NUMBER datatype in ADO

Actually the problem is not in ADO. The Oracle NUMBER type is just too large
to convert it to any known type. Variant/Decimal is ADO's best fit.

Try this:

                    ' Assign the field's value:
                    vntValue = Recordset(lngIndex - 1).Value

                    ' Excel cannot handle Decimal field types, so
                    ' this needs to be converted to a double.

                    If VarType(vntValue) = vbDecimal Then
                        vntValue = CDbl(vntValue)
                    End If

Note that you should expect a runtime error if the Oracle table contains
very large values.

Regards,

Arvid



Quote:
>Hello,

>I am using ADO 2.0 in Excel VBA, accessing data from an Oracle database.
The
>problem is that ADO does not convert the Oracle NUMBER datatype correctly.
In
>the Locals Window in VBA I see that ADO converts the NUMBER datatype
>incorrectly to "Variant/Decimal" whereas the FLOAT datatype is converted
>correctly to "Variant/Double".

>The result is that it is impossible to work with the Field containing the
>NUMBER datatype. The only workaround I have found is to use the FLOAT
>datatype in all tables which I use in the database instead of the NUMBER
>datatype. However, when you begin to work with legendary tables which use
the
>NUMBER datatype you are in trouble...

>Does anyone know how to circumvent this problem?

>Henrik



Fri, 28 Sep 2001 03:00:00 GMT
 Problem using Oracle NUMBER datatype in ADO

Using NUMBER would force oracle to preserve a large allocation and ADO's
limitations would come
to the fore.
Just specify the exact precision for the NUMBER column in the table like
NUMBER(2) or NUMBER(10,2) instead of just NUMBER.
That will take care of your problem.

Quote:

>Actually the problem is not in ADO. The Oracle NUMBER type is just too
large
>to convert it to any known type. Variant/Decimal is ADO's best fit.

>Try this:

>                    ' Assign the field's value:
>                    vntValue = Recordset(lngIndex - 1).Value

>                    ' Excel cannot handle Decimal field types, so
>                    ' this needs to be converted to a double.

>                    If VarType(vntValue) = vbDecimal Then
>                        vntValue = CDbl(vntValue)
>                    End If

>Note that you should expect a runtime error if the Oracle table contains
>very large values.

>Regards,

>Arvid



>>Hello,

>>I am using ADO 2.0 in Excel VBA, accessing data from an Oracle database.
>The
>>problem is that ADO does not convert the Oracle NUMBER datatype correctly.
>In
>>the Locals Window in VBA I see that ADO converts the NUMBER datatype
>>incorrectly to "Variant/Decimal" whereas the FLOAT datatype is converted
>>correctly to "Variant/Double".

>>The result is that it is impossible to work with the Field containing the
>>NUMBER datatype. The only workaround I have found is to use the FLOAT
>>datatype in all tables which I use in the database instead of the NUMBER
>>datatype. However, when you begin to work with legendary tables which use
>the
>>NUMBER datatype you are in trouble...

>>Does anyone know how to circumvent this problem?

>>Henrik



Tue, 02 Oct 2001 03:00:00 GMT
 Problem using Oracle NUMBER datatype in ADO

Quote:
> Although this is a good advice, it does not solve my problem entirely. The
> problem is that I am dealing with legacy tables, i.e. Oracle tables that I
> have not created. Therefore, I cannot re-create the tables and specify the
> precision.

> I was struck by your comment: "Using NUMBER would force oracle to preserve a
> large allocation". I have been told by the local Oracle Support service that
> specifying the precision of the NUMBER datatype does not affect the way
> Oracle stores the data, i.e. specifying NUMBER(2) does not use less disk
> space than using NUMBER. Is this not true?

> Best regards,
> Henrik

I would assume from your statement that the precision is merely for display
purposes.  Then why does Oracle 8.0.4 complain when I attempt to insert a value
that is larger than the precision could display with an error message like 'Insert
attempted to store a value larger than...'.

Just curious.

Mike Mattix



Sun, 07 Oct 2001 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. ADO could not get the Field value for NUMBER(38) datatype with native Oracle ODBC Driver

2. VB6 and Oracle Number Datatype Problem

3. Problems passing TEXT SQL datatype into command object using ADO

4. Stored Proc: Using Oracle RECORD datatype(%rowtype) in ADO

5. Is there a driver which will convert oracle datatype into sybase datatype ...when using with jdbc

6. fetching Number data from Oracle using ADO in VC++

7. Using a Number datatype

8. ADO is having problems with receiving numbers from Oracle DB

9. ?CAN′T ADO RETRIEVE FROM ORACLE, FIELDS WHICH DATATYPE IS LONG (IN ORACLE)

10. Oracle JDBC Thin Driver - getColumns cannot retrieve the correc number datatype

11. Oracle Datatypes: RAW x BLOB, FLOAT x NUMBER


 
Powered by phpBB® Forum Software