Showing posts from August, 2017

Convert BLOB to CLOB in Oracle

Solution: Step 1:  Create plsql function  blob_to_clob . It should return the CLOB . CREATE OR REPLACE   FUNCTION blob_to_clob (       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 := utl_raw.cast_to_varchar2(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_clob ; Step 2:  Call the function and test it . Many ways to check this funtionality, Method 1:  Generate XML data from relational content Convert CLOB (XML data) to BLOB Convert BLOB to CLOB Print the CLOB DECLARE   l_xml CLOB;   l_clob_to_blob BLOB;   l_blob_to_clob CLOB; BEGIN   -- Generate XML