Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Numeric overflow while converting clob to blob

645141Jun 20 2008 — edited Jun 20 2008
Hello!

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!

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 18 2008
Added on Jun 20 2008
2 comments
1,127 views