Extracting data from Oracle Table to Access Table

[admin]

Hi All,

We are trying to extract all the records from one of our Oracle tables using
some filter through the Transform to be ported into the Access Tables, but
were facing problems which I am listing out below :

1. Mismatch in Data types:
2. Expected and Actual column size mappings

Now here as, I have tried to regularize the data type mismatch by
synchronizing the data types at both the ends, but the 2nd problem still
exists and I am not able to find a solution, especially with those columns
where the column size in the Oracle table is "1" and for the same column in
Access Table is "5", now here while writing data into the Access Table it is
not allowing me to write into the Access Table which is quite clearly
frustrating as at the time of creation of the table we had not specified any
length and it has taken the default length of 5 for Small Integer type of
data.

Here I am not able to understand why is Access getting adamant on filling
the complete length of the column where as it can accept 1 digit instead of
5.

I hope I am clear while explaining the problem. Please, please, please do
help me out in this as early as possible.

Thanks & Regards
Kedar

[admin]

Kedar

What I was trying to point out and did not explain very well was that
what Access calls a small integer and what Oracle calls a small integer
are not the same. It is okay to make these different data types if that
is what it takes to get rid of the error. You need to figure out exactly
what data type Access needs in order to let the data pass. Access has a
limit on record size of 1028. I have hit that limit as well. It gives a
real weird error message. It sounds like you know Access very well. I
think if you play with the table definition in Access then it will work.

Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com


-----Original Message-----
From: Terdalkar, Kedar R. [mailto:Kedar.Terdalkar@awi.state.fl.us]
Sent: Friday, November 14, 2003 1:14 PM
To: 'datastage-users@oliver.com'
Subject: RE: Extracting data from Oracle Table to Access Table

Hi Kim,

Here my source is Oracle and the target is Access. This is required as
our
clients have certain reports developed based on Access DB which don't
want
to discontinue using them and hence are asking this transfer of related
data
from Oracle to Access.

And yes, thanks a ton for the suggestion, but I have this work around
already in use, after which it is now not giving the datatype mismatch
problem, but now facing the second problem which I had mentioned in my
earlier mail.

Thanks & Regards
Kedar

-----Original Message-----
From: Kim Duke [mailto:Kim_G_Duke@Yahoo.com]
Sent: Friday, November 14, 2003 1:41 PM
To: datastage-users@oliver.com
Subject: RE: Extracting data from Oracle Table to Access Table

Kedar

Here are a couple of tricks. Link to the source Oracle table from
Access. Look at the table definition in Access. It will not let you
modify it but see what it defines the column in question to be. Make the
target column the same. Access is very picky. It makes integers into
numbers and all kinds of weird things even with varchars. This should
fix it.

Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com


-----Original Message-----
From: Terdalkar, Kedar R. [mailto:Kedar.Terdalkar@awi.state.fl.us]
Sent: Friday, November 14, 2003 11:31 AM
To: 'datastage-users@oliver.com'
Cc: Kedar Terdalkar (E-mail)
Subject: Extracting data from Oracle Table to Access Table

Hi All,

We are trying to extract all the records from one of our Oracle tables
using
some filter through the Transform to be ported into the Access Tables,
but
were facing problems which I am listing out below :

1. Mismatch in Data types:
2. Expected and Actual column size mappings

Now here as, I have tried to regularize the data type mismatch by
synchronizing the data types at both the ends, but the 2nd problem still
exists and I am not able to find a solution, especially with those
columns
where the column size in the Oracle table is "1" and for the same column
in
Access Table is "5", now here while writing data into the Access Table
it is
not allowing me to write into the Access Table which is quite clearly
frustrating as at the time of creation of the table we had not specified
any
length and it has taken the default length of 5 for Small Integer type
of
data.

Here I am not able to understand why is Access getting adamant on
filling
the complete length of the column where as it can accept 1 digit instead
of
5.

I hope I am clear while explaining the problem. Please, please, please
do
help me out in this as early as possible.

Thanks & Regards
Kedar