how to convert long data type to clob - error ORA-06502: PL/SQL: numeric or value error

I'm trying to copy data from long data type to clob by copying data from one table to another using pl/sql procedure.
I'm getting error - ORA-06502: PL/SQL: numeric or value error while fetching long data type.
I assume lenght exceed 32k. How can I solve this issue?
The logic is very simple:
CREATE OR REPLACE PROCEDURE convert_long
AS
 CURSOR long_text_c
   IS
      SELECT id, long_field FROM table_long_text;
BEGIN
   FOR v_long_text IN long_text_c
   LOOP
           INSERT INTO table_clob_text
                        (id,                     clob_field)
           VALUES (v_long_text.id, v_long_text.long_field);
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/
tribuna33Asked:
Who is Participating?
 
ariohConnect With a Mentor Commented:
CREATE OR REPLACE PROCEDURE convert_long
AS
 CURSOR long_text_c
   IS
      SELECT id, long_field FROM table_long_text;

  tRowID varchar2( 100 );
  tCLOB CLOB;
BEGIN
   FOR v_long_text IN long_text_c
   LOOP
     INSERT INTO table_clob_text ( id, clob_field )
     VALUES ( v_long_text.id, EMPTY_CLOB() )
      returning ROWID into tRowID;
     select CLOB_FIELD into tCLOB from table_clob_text where rowid = tRowID for update;
     dbms_lob.write( tCLOB, length( to_char( v_long_text.long_field ) ), 1, to_char( v_long_text.long_field ) );
   END LOOP;
  COMMIT;
EXCEPTION
   WHEN OTHERS  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/
0
 
konektorConnect With a Mentor Commented:
only if you are moving data from table1.long_column to table2.clob_column (or blob) you can use function to_lob
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions185.htm#i79464
you can use it only in insert-select statement, but it's your case.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.