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
  
  SELECT dbms_xmlgen.getxml('select * from emp') xml 
    INTO l_xml 
    FROM dual;
  
  -- Convert CLOB to BLOB
  
  l_clob_to_blob := clob_to_blob (l_xml);
  
  -- Convert BLOB to CLOB
  
  l_blob_to_clob := blob_to_clob(l_clob_to_blob);
  
  -- Print the CLOB
  print_clob_to_output (l_blob_to_clob);

END;

Method 2: 

• Store BLOB in database
• Convert BLOB to CLOB
• Print the CLOB

CREATE TABLE store_blob_clob_files
  (
    ID NUMBER,
    blob_content BLOB,
    clob_content CLOB
  );

DECLARE
  l_blob BLOB;
  l_clob_to_blob BLOB;
  l_blob_to_clob CLOB;
BEGIN
  -- Get BLOB file
  
  SELECT blob_content
    INTO l_blob 
    FROM store_blob_clob_files
   WHERE id = 1;
  
    -- Convert BLOB to CLOB
  
  l_blob_to_clob := blob_to_clob(l_blob);
  
  UPDATE store_blob_clob_files 
     SET clob_content = l_blob_to_clob
   WHERE ID = 1;
  COMMIT;
  
  -- Print the CLOB
  print_clob_to_output (l_blob_to_clob);

END;

Output:

Fig 1: Empty CLOB Content

Fig 2: CLOB Loaded

Related Posts:

• Generating XML data from relational data using Oracle SQL/XML
• Print CLOB data using Oracle PL/SQL
• Convert CLOB to BLOB in Oracle