SQL & PL/SQL

Hi!

I have a table that stores pictures as BLOB. Due to some project rules, I need to be able to convert this image to a CLOB (represented as a HEX String) and, after that, I need to convert this HEX String back to BLOB.

I already created the function that converts BLOB to HEX. See below:

CREATE OR REPLACE FUNCTION blob_to_hex (blob_in IN BLOB)
RETURN CLOB
AS
    v_clob    CLOB;
    v_varchar VARCHAR2(32767);
    v_start   PLS_INTEGER := 1;
    v_buffer  PLS_INTEGER := 32767;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    
    FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
    LOOP
        
       v_varchar := DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start);
       DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
       v_start := v_start + v_buffer;
    END LOOP;
    
   RETURN v_clob;
  
END blob_to_hex;

But, I´am having some difficulties to convert the HEX CLOB back to BLOB.

Does anybody know how could I do this?

Thanks a lot!!!

Regis