In an old application (no source code available) long texts with over 40.000 characters are stored in an Oracle database in a table column of type LONG RAW. We now want to transfer the texts into another Oracle database and want to display their content. Somehow the old application was capable to do so. However, we always run into a 4000 byte limit...

How can we export/import and display the content in a human readable VARCHAR2 (or multiple ones).

All convert functions we tried seam not to work. For example TO_LOB or TO_CLOB.

ORA-00932: Inconsistent datentype: - expected, LONG BINARY found
ORA-00932: Inconsistent datentype: CHAR expected, LONG BINARY found

CodePudding user response：

From the documentation:

TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement.

So you can't do:

select to_lob(old_column) from old_table;

ORA-00932: Inconsistent datentype: - expected, LONG BINARY found

But you can move the data into a BLOB column in another table:

insert into new_table(new_column)
select to_lob(old_column) from old_table;

db<>fiddle

Once you have the data as a BLOB you can manage it as a binary value, or if it represents text then you can convert it to a CLOB - using the appropriate character set - with the dbms_lob package.