Skip to main content

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:

Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  
5 comments
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename
9 comments
Read more