beginner: LONG, BLOB, CLOB

[Martin Trzaskalik]

I am currently trying to migrate from a DBase data base to Oracle
8.0.5.0.

I imported the tables from DBase into an Access 97 Database and
would like to use ODBC to get them into Oracle.

Several tables have multiple memo fields so I can not use
LONG to stored them because only one LONG column is allowed by
Oracle.

I tried to migrate them as CLOBs.
However when I try to insert a Record which has a memo with more
than 4000 bytes I get an Oracle Error:

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

When I insert rows with memos with less than 4000 bytes I do not
get this error.

Why is it that CLOB behaves just like VARCHAR2 even if it is a LOB?

Thanks,

Martin

[Dan]

> I am currently trying to migrate from a DBase data base to Oracle
> 8.0.5.0.
>
> I imported the tables from DBase into an Access 97 Database and
> would like to use ODBC to get them into Oracle.

In one word "DON'T!"

Access is not ANSI SQL compliant and can make a horrifying mess of the
Oracle Data Dictionary. The best thing to do would be to sit down and
create a new ERD that takes full advantage of Oracle 8's capabilities
and build the tables that you need from schema manager or SQL*Plus.

Daniel A. Morgan

[MotoX]

I've got to admit I know little about ODBC/Access. I have seen people
migrate using the Migration Tool on the Oracle Web site - have you tried
this?

MotoX.

Martin Trzaskalik wrote in message
<36483CA4...@ernie.mi.uni-koeln.de>...

>I am currently trying to migrate from a DBase data base to Oracle
>8.0.5.0.
>
>I imported the tables from DBase into an Access 97 Database and
>would like to use ODBC to get them into Oracle.
>

[Martin Trzaskalik]

> > I am currently trying to migrate from a DBase data base to Oracle
> > 8.0.5.0.
> >
> > I imported the tables from DBase into an Access 97 Database and
> > would like to use ODBC to get them into Oracle.
>

> In one word "DON'T!"

> Access is not ANSI SQL compliant and can make a horrifying mess of the
> Oracle Data Dictionary. The best thing to do would be to sit down and
> create a new ERD that takes full advantage of Oracle 8's capabilities
> and build the tables that you need from schema manager or SQL*Plus.

But how do I get the data in? SQL*Plus does not accept more than 2399
chars by line but due to the "memos" my INSERT statments are quite longer.

Is the only way to bind the ROW using the API?

Martin

[Martin Trzaskalik]

> I've got to admit I know little about ODBC/Access. I have seen people
> migrate using the Migration Tool on the Oracle Web site - have you tried
> this?

Yes, but I think it does only support LONG and VARCHAR2...

BTW. There ist a ODBC-Migration Tool to. But it returns something like
"invalid string length" when trying to use LONG.
I guess it is to old to work with Oracle 8.0.5.

Martin

[Dan]

> But how do I get the data in? SQL*Plus does not accept more than 2399
> chars by line but due to the "memos" my INSERT statments are quite longer.
>
> Is the only way to bind the ROW using the API?

You can import the data using Access, though I would use SQL*Loader. What you
should never do is build objects using Access. Just build the tables first and
then perform the import.

Daniel A. Morgan