Re: Convert Tables - Access to Oracle

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 04 Dec 2004 01:07:50 GMT
Message-ID: <G58sd.38109$6q2.14115@newssvr14.news.prodigy.com>

NetComrade wrote:

> Check oracle site for 'migration' tools. I am not sure if they have
> one for Access.
>
> On Fri, 3 Dec 2004 13:28:17 -0500, "Jason Gyetko"
> <jgyetko_at_epower-inc.com> wrote:
>
>

>>Does anyone know of a good Access to Oracle conversion process or program?
>>I tried Intelligent Converters - Access to Oracle v1.3, which converted all
>>tables successfully, but I am having issues with the Number fields that were
>>converted.  When I link to the Oracle DB from Access, the Number fields are
>>shown as Text instead of Number, but when I look at them in Oracle they are
>>Number fields.  The conversion made the Size property of the Number field
>>38.  If I change that to 0 my Access links are ok.  Thanks.
>>
>>

>
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

This is not an Access to Oracle conversion issue, at least not directly. It appears to be a known limitation of MS Access connecting to Oracle, regardless of how your Oracle table definition came to be.

http://support.microsoft.com/kb/q104977/

What you probably want is for your Oracle datatype to be declared as NUMBER() rather than NUMBER(38) when converted from Access (implicit rather than explicit). I've had good success using Perl DBI (DBD::Oracle and DBD::ODBC) and rolling my own. DBI provides scale and precision settings for datatypes in each database, so it's pretty easy to map them however you want.

Not sure what your package or Oracle Migration Workbench do, but if you look around you may find some way to change the default behavior.

-Mark Bole Received on Fri Dec 03 2004 - 19:07:50 CST