Re: MSACCES.EXE to ORACLE via odbc implicit float number conversion
From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Thu, 29 Apr 2010 00:23:34 -0700 (PDT)
Message-ID: <403a6c38-8f43-454d-aa53-b3ba69ea9952_at_l32g2000yqm.googlegroups.com>
On 28 apr, 18:17, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Apr 28, 6:39 am, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
>
> > Hi,
>
> > Perhaps there is someone with knowledge about MSACCESS and ORACLE.
> > A new application of one of my customers uses MSACCESS and ORACLE with
> > ODBC between them.
>
> > During 10046 level 12 tracing I saw bind variables type float
> > (oacdty=101).
> > The referenced columns are type number so a conversion takes place.
> > This causes the optimizer to ignore the index.
>
> > Are there any issues regarding floats and numbers and ODBC to Oracle?
>
> > Regards Hans-Peter
>
> Prior to 11g Oracle stores all numeric data as data type number. If
> you created a table in Oracle with a column such as scientific_value
> float then interally it was a number data type that was constrained by
> Oracle to only handle values that fit in a float.
>
> When passing float data via ODBC/OLE conversion takes place.
>
> What does the explain plan look like?
>
> HTH -- Mark D Powell --
Date: Thu, 29 Apr 2010 00:23:34 -0700 (PDT)
Message-ID: <403a6c38-8f43-454d-aa53-b3ba69ea9952_at_l32g2000yqm.googlegroups.com>
On 28 apr, 18:17, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Apr 28, 6:39 am, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
>
> > Hi,
>
> > Perhaps there is someone with knowledge about MSACCESS and ORACLE.
> > A new application of one of my customers uses MSACCESS and ORACLE with
> > ODBC between them.
>
> > During 10046 level 12 tracing I saw bind variables type float
> > (oacdty=101).
> > The referenced columns are type number so a conversion takes place.
> > This causes the optimizer to ignore the index.
>
> > Are there any issues regarding floats and numbers and ODBC to Oracle?
>
> > Regards Hans-Peter
>
> Prior to 11g Oracle stores all numeric data as data type number. If
> you created a table in Oracle with a column such as scientific_value
> float then interally it was a number data type that was constrained by
> Oracle to only handle values that fit in a float.
>
> When passing float data via ODBC/OLE conversion takes place.
>
> What does the explain plan look like?
>
> HTH -- Mark D Powell --
It seems to have to do with the ODBC driver see; Full Index Scan in Execution Plan With Access and Double or Float as Primary Key [ID 403198.1] on Metalink Received on Thu Apr 29 2010 - 02:23:34 CDT