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.

clob column update

cubeguyApr 11 2023 — edited Apr 11 2023

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
           --:x := DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21)));
                   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;
  -- dbms_lob.createtemporary(src_lob, true);
    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;  
   --  dbms_xslprocessor.clob2file(clobfromblob ( G_BLOB ), 'EXPORT_DIR', '620828.sql');  
   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;
/

Comments

Processing

Post Details

Added on Apr 11 2023
3 comments
262 views