access 97 "memo" to oracle 8i

[cubi]

Hi,

I have a question on how to get data from a "memo" field in Access 97 to Oracle 8.1.6.

I created the Oracle database, make an ODBC link to that database from Access 97, and copied the data over by:

insert into oracle_table1
select * from access97_table1

and get the error:

ora-01461: can bind a LONG value only for insert into a LONG column.

table1 in Access97 has a few fields with "memo" data type and I think this is the problem; data in other tables, with no "memo" fields, in the database copied over just fine. The fields in the Oracle database that receive these "memo" fields are of VARCHAR2(4000) data type.

Any suggestions appreciated. I am converting a database from Access97 to Oracle8i, and this particular table, with its "memo" fields, is the only remaining obstacles.

Thanks.

 

[Turkbear]

Hi,
Sadly, Access to Oracle results in some problems, this is one of them..MEMO fields in Access are treated as LONG in Oracle and must, therefore, be inserted into LONG datatype
fields..Change the Access type to char ( or text, I forget what it uses) and it will solve your problem.
You might also want to check the Oracle Migration Utility to see if it offers more flexibility..

 

[vgg]

Hi,
I would suggest you try pushing the 'memo' data
as CLOB data in ORACLE.
It should work fine.

vg

 

[cubi]

Thanks for all your replies. Pushing "memo" to CLOB works!

 

[Turkbear]

Hi,
Glad the CLOB solution worked, but, remember that CLOB fields need special handling ( using the DBMS_LOB package) and not 'standard' Sql.