Numeric overflow while converting clob to blob
645141Jun 20 2008 — edited Jun 20 2008Hello!
I am trying to convert clob fields to blob, cause i am using text control wich only can access to blob fields at target database. I have performed a migration from informix to oracle, using OMWB, and its data maping convert text fields from informix to clob fields in oracle.
I am using the following function i got from this forum to convert clob fields to blob:
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION c2b( c IN CLOB )
RETURN BLOB
-- typecasts CLOB to BLOB (binary conversion)
IS
pos PLS_INTEGER := 1;
buffer RAW( 32767 );
res BLOB;
lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
BEGIN
DBMS_LOB.createTemporary( res, TRUE );
DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
LOOP
buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos )
);
IF UTL_RAW.LENGTH( buffer ) > 0 THEN
DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
END IF;
pos := pos + 16000;
EXIT WHEN pos > lob_len;
END LOOP;
RETURN res; -- res is OPEN here
END c2b;
-----------------------------------------------------
but i have clob fields larger than pls_integer range in bytes, and, when i run this function over the clob fields to convert to, i got this error:
SQL> UPDATE table_name SET blob_col = c2b(clob_col);
SET blob_col = c2b(clob_col)
*
ERROR at line 2:
ORA-01426: numeric overflow
ORA-06512: at "owner.C2B", line 20
Anyone knows how to solve it? I have tried with number instead of pls_integer, but it is too slow converting, and seems to be crashed.
Thanks!