Oracle Version 19c.
I've compressed the BLOB data and I want to uncompress it and update it CLOB in column. However it's throwing an error
"[Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 28" due to utl_raw.cast_to_varchar2.
T1 and T2 are the same structure and column objectid being PK. How to over come this error
create table t1 (objectid integer primary key, CPAYLOAD nclob);
var x nclob;
begin
:x := DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21)));
loop
exit when dbms_lob.getlength(:x) > 14000000;
:x := :x || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21)));
end loop;
end;
/
begin
insert into t1 values (620828,:x);
commit;
end;
/
declare
src_lob CLOB;
G_CLOB clob;
pos INTEGER := 1;
buf VARCHAR2(32767);
v_len INTEGER := 4000;
v_blob blob := null;
G_BLOB BLOB ;
V_SQL varchar( 4000 );
BEGIN
SELECT CPAYLOAD INTO src_lob FROM t1 where objectid = 620828;
for L_I IN 1..ceil(dbms_lob.getlength( src_lob ) / v_len ) loop
buf := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(src_lob, v_len, pos));
V_BLOB := hextoraw( buf );
if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
if nvl( dbms_lob.getlength( G_BLOB ), 0 ) = 0 then
G_BLOB := utl_compress.lz_compress(V_BLOB);
else
DBMS_LOB.APPEND( G_BLOB, V_BLOB );
end if;
end if;
pos := pos + v_len;
end loop;
V_SQL := 'update t2 SET DPAYLOAD= :1 where objectid = 620828';
G_CLOB := utl_raw.cast_to_varchar2(utl_compress.lz_uncompress(rawtohex(G_BLOB)));
execute immediate V_SQL using G_CLOB;
END;
/