migrating access97 to oracle73 with many memos in one table

I have just recently helped a project convert from an Access DB to Oracle, where there were mulitple tables with memo fields. Some tables even had more than one memo field. In this particular case, I determined it would be better in the long run to use varchar(4000) (we are using 8.0.5 rather than 7.3, so you would need to size down your fields to 2000).
In my case, we could not export the access tables to flat files, then use sqlldr, because the memo fields contained carraige returns. So I had the developer attach the Oracle tables to his access db, and create insert scripts to insert the data from each access table to the oracle table. For the really large tables, I altered the tables to be nologging. This keeps the rollback segs from being used during the load. Then after the load, alter the tables back to logging. The load sql was like this:
INSERT INTO attachedTable

     SELECT * FROM localTable

hth ~Jim

On Sun, 14 Nov 1999 13:24:07 +0100, d_b <d_brammen_at_yahoo.de> wrote:

>Hi there!
>
>Although I scaned this discussion group and the oracle one I don't have
>any idea to go on. My Access database has some tables with ten or more
>memo fields. I tried the Migration Tool from Oracle 73 but beside the
>point no data in this fields is longer than 512 bytes it crashes because
>"there can be only one long datatype per table".
>Using ODBC to connect to oracle and export the tables manually there is
>useless - same error.
>Does anyone have a good idea how I can get my data into oracle? The way
>may be strange, important is that it is working.
>
>Thanxs, D.
Received on Sun Nov 14 1999 - 19:26:17 CST