Trouble copying 4000 chars from CLOB into VARCHAR2(4000 CHAR)
4996Aug 12 2010 — edited Aug 20 2010I'm trying to migrate some data from a table where it is currently stored in a CLOB to another table where I have declared a column of VARCHAR2(4000 CHAR).
I've declared a cursor as either:
a) select columna, columnb, substr(clob_column,1,4000) from...
b) select columna, columnb, dbms_lob.substr(clob_column,4000,1) from...
I then LOOP and FETCH the substr value into a variable that is declared as VARCHAR2(4000 CHAR)
With cursor version 'a', using the regular subtr function, I get: ORA-01461: can bind a LONG value only for insert into a LONG column
With cursor version 'b', using the dbms_lob.substr function, I get: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I'm using Oracle 10.2.0.4.0
I'm not even sure what to look at next?