Implementación de la función HEXTORAW para dar soporte a CLOBS:
create or replace procedure hextoblob( src_clob in clob, dest_blob in out nocopy blob ) is v_pos number := 1; v_amount number := 1024; v_buffer varchar2(1024); v_raw raw(2048); begin while v_pos < dbms_lob.getlength(src_clob) loop dbms_lob.read( lob_loc => src_clob, amount => v_amount, offset => v_pos, buffer => v_buffer); v_raw := hextoraw(v_buffer); dbms_lob.writeappend( lob_loc => dest_blob, amount => utl_raw.length(v_raw), buffer => v_raw); v_pos := v_pos + v_amount; end loop; end hextoblob;
Para probar el procedimiento podemos ejecutar el siguinte código:
declare cl_test clob := '4041424344'; bl_result blob; begin dbms_lob.createtemporary( lob_loc => bl_result, cache => false, dur => dbms_lob.call); hextoblob( src_clob => cl_test, dest_blob => bl_result); dbms_output.put_line('Result: '|| utl_raw.cast_to_varchar2( bl_result )); dbms_lob.freetemporary( bl_result ); end;
El resultado debe ser:
Result: @ABCD