data import from ms-access

[ramming]

Hi folks,
I got a problem with a database import from an ms-access database, when using more than one CLOB-column.
I created an ODBC-connection to the access-db, so I can use the COPY-command to import the data to an existing oracle-db. This works very well. But the problem concerns memo-fields from the access-db. I defined those fields as CLOBs in oracle, because one table needs more than one columns of this type. The COPY-command is restricted to use only one CLOB-column per statement. So I first filled my oracle-table with only one CLOB-column, copied the second CLOB-column and the primary key columns into a temp. table and tired then to update my oracle-table, but then I get an ORA-Message "inconsistent datatypes". In the temp. table the memo-field is stored as "long" datatype, but in the COPY-command I use the function TO_LOB() to convert it to a CLOB, which works well for the first column, but not for the second.
Any help or comments would be appreciated.
Regards

[manjunathk]

Which is your driver used for connecting? Microsoft ODBC for Oracle does not support CLOB. Your problem might have something to do with that.
Try changing to Oracle ODBC driver .
Thanks
manjunath

[ramming]

Thanks for your reply. But it's not true that the MS ODBC driver doesn't support CLOBs. I can use the COPY command and import a CLOB column, but only one per table. But I need to import two columns of that type.
I downloaded the Oracle ODBC driver (Version 8.1.6.2) but this works only for connections from access to oracle. I need a connection from oracle to access.
Or did I get something wrong ?
Greetings ...

[johng]

Have you tried a work around of creating a two column table in access, primary key and second CLOB collumn and then reassembling in Oracle? I have had to do this before. Have you treid the Oracle-MSAccess Import wizard that installs with Oracle for NT? just two suggestions.

John

[manjunathk]

Hi Ramming,
about MS ODBC for Oracle supporting the CLOB/BLOB etc, it is better you check once more. We could not access data through the application. It is then we checked up microsoft site for info and found the following:
**********************************
The connectivity mechanism used by Oracle 8i is slightly different from the OCI Connectivity that was prevalent in earlier releases of Oracle. The Microsoft ODBC for Oracle driver (MSORCL32.DLL version 2.573.4403) and the OLEDB provider for Oracle (Msdaora.dll version 2.50.4403) that are released with MDAC 2.5, as well as later versions of the driver and provider, have been tested and support connectivity to Oracle 8i. None of the earlier versions of this driver and the provider support this behavior.

However, the MDAC 2.5 and 2.6 versions of the Microsoft Oracle ODBC driver and the OLEDB provider do not support many other Oracle 8.x specific features including the new datatypes. Datatypes such as CLOB, BLOB, BFILE, NCHAR, NCLOB, NVARCHAR2 (which were introduced with Oracle 8.x) are not supported using this version or earlier versions of the Microsoft driver and provider for Oracle.

********************************

For more information in this you can visit:
[url]http://support.microsoft.com/support/kb/articles/Q244/6/61.ASP[/url]
thanks
manjunath

[ramming]

Hi people,
I solved the problem on my own by now. I created a second temp. table which consists of the primary key columns and the second CLOB column defined as CLOB. I inserted the records of the first temp. table which contains the imported records from access. Then I updated my original oracle table with the records of the second temp. table. This way worked.
Any way, thanks for your support. But I'm interested in the Oracle-MS Access Import wizard mentioned by johng. Where do I find this tool ? I'm using Oracle8i (8.1.6) for Linux, but I have installed the Oracle Enterprise Manager Suite on my Win2K client.
To manjunathk, my MS ODBC for Oracle driver is a later version than the one mentioned by you. But probably the article at support.microsoft.com is interesting to me, because I'm having further problems in importing OLE-objects from access as BLOB columns