Posts

Showing posts from September, 2017

Convert CLOB to BLOB using Oracle PLSQL

Image
Solution: Step 1:  Create plsql function  clob_to_blob . It should return the BLOB . create or replace  FUNCTION clob_to_blob (       p_clob_in IN CLOB)     RETURN BLOB   IS     v_blob BLOB;     v_offset         INTEGER;     v_buffer_varchar VARCHAR2(32000);     v_buffer_raw RAW(32000);     v_buffer_size binary_integer := 32000;   BEGIN     IF p_clob_in IS NULL THEN       RETURN NULL;     END IF;     dbms_lob.createtemporary(v_blob, TRUE);     v_offset := 1;     FOR i IN 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size)     loop       dbms_lob.READ(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);       v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);       dbms_lob.writeappend(v_blob, utl_raw.LENGTH(v_buffer_raw), v_buffer_raw);       v_offset := v_offset + v_buffer_size;     END loop;     RETURN v_blob;   END clob_to_blob ; Step 2: Call the function and test it . DECLARE   l_xml CLOB;   l_blob BLOB;   l_clob_to_blob BLOB;