Convert CLOB to BLOB using Oracle PLSQL

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;
l_file_name VARCHAR2(200);
l_id NUMBER;
BEGIN
-- Generate XML

SELECT dbms_xmlgen.getxml('select * from emp') xml
INTO l_xml
FROM dual;

-- Print CLOB

print_clob_to_output (l_xml);

-- Convert CLOB to BLOB

l_clob_to_blob := clob_to_blob (l_xml);

-- Set File Name

l_file_name := 'Employee Management Report (' ||to_char(SYSDATE,'DDMMYYYYHHMISS') ||')' ||'.xml';

-- Send mail - Converted BLOB as attachment

l_id := apex_mail.send( p_to => 'orclking@yyy.com',
p_from => 'xxx@yyyy.com',
p_subj => 'Employee Management Report',
p_body => 'Please review the attachment.',
p_body_html => 'Please review the attachment'
);

apex_mail.add_attachment ( p_mail_id => l_id,
p_attachment => l_clob_to_blob,
p_filename => l_file_name,
p_mime_type => 'xml'
);
END;

Related Posts: