NLS, Part DeuxJune 10th, 2011 — ddelmoli
A guest post today, by Brian Ledbetter, a co-worker at Agilex:
On a customer’s database, we ran across a table that would not migrate. It was admittedly a log table, containing long chunks of HTTP header data, but whenever we tried importing it into our 11gR2 database, we ended up getting:
IMP-00058: ORACLE error 1461 encountered
ORA-01461: can bind a LONG value only for insert into a LONG column
After looking at the table structure, the first thing we noticed was that there was a VARCHAR2(4000) column in the table. Considering that this column was already the maximum size (in bytes) for a CHAR-based data type, it became the focus of our attention.
Looking online for solutions, we found references  suggesting that Oracle was implicitly converting this column to a VARCHAR2(4000 CHAR) type, creating a column that can contain up to 4 bytes per character. Because this overflows the 4000 byte limit on column length, Oracle then attempted to implicitly convert the datatype to a LONG VARCHAR2, which is apparently deprecated in 11gR2. (We’re not sure why Oracle is still trying to make this conversion, if that’s the case.)
Anyway, we tried precreating the table with a CLOB datatype, and that didn’t work either, so as a workaround, we created a copy of the table with the data trimmed to 1000 characters (leaving plenty of room after UTF8 conversion):
create tabname_migtmp as select col1, col2, substr(col3,1,1000) col3 from tabname;
We then used exp/imp to copy tabname_migtmp over to the 11gR2 server, and inserted the data from it into the final location.
insert into tabname select * from tabname_migtmp;
drop table tabname_migtmp;
See Also: Technote 444171.1, https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=444171.1