Skip to main content

Send an Oracle Database Table Data in Excel File as Mail Attachment using a Simple PL/SQL Excel & APEX Mail APIs

  

Here I am giving an example to send an oracle database table data in excel sheet as mail attachment using a simple PL/SQL & APEX Mail APIs.

What is ORA_EXCEL?

Oracle PL/SQL to Excel XSLX API.

ORA_EXCEL (Oracle Excel) is a PL/SQL package for Oracle database that produces Excel XLSX documents.

ORA_EXCEL enables developers to create Excel reports or export data from an Oracle database to Excel files using a simple PL/SQL Excel API.

Step 1: Download ORA_EXCEL pkg (wrapped pkg) and Install it in your schema.

Click here to Download ORA_EXCEL

Step 2: Create a new procedure as below, which should take care of following actions,
  •  Convert table data into excel file.
  •  Save generated excel file to PL/SQL BLOB variable type or Oracle Directory.
  •  Send generated excel file as an attachment.
CREATE OR REPLACE 
PROCEDURE table_data_to_excel_file(x_return_status out VARCHAR2,
                                    x_return_message out VARCHAR2)
AS
  CURSOR cur_emp_data
  IS
    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 emp emp,
           dept dept,
           emp mgr
     WHERE emp.deptno = dept.deptno
       AND emp.mgr      = mgr.empno (+)
    ORDER BY emp.ename ASC;

   l_from          VARCHAR2(100) := 'fyi@yourorganization.com';
   l_to            VARCHAR2(100) := 'pmo_desk@yourorganization.com';
   l_body          CLOB;
   l_body_html     CLOB;
   l_attach_mime   VARCHAR2(200) := 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
   l_generated_file  BLOB;
   l_id            VARCHAR2(200);
BEGIN
  x_return_status := 'S'; 
  x_return_message := 'Process Completed';
  
  /* Set up Header */
  
  ora_excel.new_document;
  ora_excel.add_sheet('Employee Details');
  ora_excel.add_row;
  ora_excel.add_style(style_name => 'header_style', border => TRUE, border_style => 'thin', border_color => 'ffffff', vertical_align => 'middle', horizontal_align => 'center', font_name => 'Tahoma', font_size => 10, wrap_text => TRUE, color => 'ffffff', bg_color => '00008B');
  ora_excel.add_style(style_name => 'data_style', border => TRUE, border_style => 'thin', border_color => '000000', vertical_align => 'middle',font_name => 'Tahoma', font_size => 10, wrap_text => FALSE );
  ora_excel.set_cell_value('A', 'Emp. No.');
  ora_excel.set_column_width('A', 20);
  ora_excel.set_cell_style('A', 'header_style');
  ora_excel.set_cell_align_left('A');
  ora_excel.set_cell_value('B', 'Emp. Name');
  ora_excel.set_column_width('B', 20);
  ora_excel.set_cell_style('B', 'header_style');
  ora_excel.set_cell_align_left('B');
  ora_excel.set_cell_value('C', 'Job');
  ora_excel.set_column_width('C', 20);
  ora_excel.set_cell_style('C', 'header_style');
  ora_excel.set_cell_align_left('C');
  ora_excel.set_cell_value('D', 'Department');
  ora_excel.set_column_width('D', 20);
  ora_excel.set_cell_style('D', 'header_style');
  ora_excel.set_cell_align_left('D');
  ora_excel.set_cell_value('E', 'Location');
  ora_excel.set_column_width('E', 20);
  ora_excel.set_cell_style('E', 'header_style');
  ora_excel.set_cell_align_left('E');
  ora_excel.set_cell_value('F', 'Manager');
  ora_excel.set_column_width('F', 20);
  ora_excel.set_cell_style('F', 'header_style');
  ora_excel.set_cell_align_left('F');
  ora_excel.set_cell_value('G', 'Hiredate');
  ora_excel.set_column_width('G', 20);
  ora_excel.set_cell_style('G', 'header_style');
  ora_excel.set_cell_align_right('G');
  ora_excel.set_cell_value('H', 'Salary');
  ora_excel.set_column_width('H', 20);
  ora_excel.set_cell_style('H', 'header_style');
  ora_excel.set_cell_align_right('H');
  ora_excel.set_cell_value('I', 'Commission');
  ora_excel.set_column_width('I', 20);
  ora_excel.set_cell_style('I', 'header_style');
  ora_excel.set_cell_align_right('I');
  
  /* Construct data for each rows */
  
  FOR emp_data IN cur_emp_data
  loop
    ora_excel.add_row;
    ora_excel.set_cell_value('A', emp_data.empno);
    ora_excel.set_cell_style('A', 'data_style');
    ora_excel.set_cell_value('B', emp_data.ename);
    ora_excel.set_cell_style('B', 'data_style');
    ora_excel.set_cell_value('C', emp_data.JOB);
    ora_excel.set_cell_style('C', 'data_style');
    ora_excel.set_cell_value('D', emp_data.department);
    ora_excel.set_cell_style('D', 'data_style');
    ora_excel.set_cell_value('E', emp_data.LOCATION);
    ora_excel.set_cell_style('E', 'data_style');
    ora_excel.set_cell_value('F', emp_data.manager);
    ora_excel.set_cell_style('F', 'data_style');
    ora_excel.set_cell_value('G', emp_data.hiredate);
    ora_excel.set_cell_style('G', 'data_style');
    ora_excel.set_cell_value('H', emp_data.salary);
    ora_excel.set_cell_style('H', 'data_style');
    ora_excel.set_cell_value('I', emp_data.commission);
    ora_excel.set_cell_style('I', 'data_style');
  END loop;
  
  /* Store generated file into plsql blob variable */
  
  ora_excel.save_to_blob(l_generated_file);
  
    l_body      := 'to view the content of this message, please use an html enabled mail client.' || utl_tcp.crlf;
    l_body_html := '<html><head>               
                    <style type="text/css">                
                    body{font-family: tahoma, arial, helvetica, sans-serif;                
                    font-size:10pt;                
                    color:#002060;                
                    margin:30px;                
                    background-color:#ffffff;}                
                    span.sig{font-style:italic;                
                    font-weight:bold;                
                    color:#811919;}</style></head>                
                    <body>Hi,<br/><br/>Please find attached. Kindly review it and do the necessary action (if required).</p>'
                    ||'<br/><span style= font-size:10px;>Note: This is a system generated email, do not reply to this.</span><br/></body></html>';
    
    /* Send mail with generated file */
    
    l_id := apex_mail.send(p_to        => l_to,
                           p_from      => l_from,
                           p_subj      => 'Employee Details ['||to_date(SYSDATE,'DD-Mon-RRRR')||']',
                           p_body      => l_body,
                           p_body_html => l_body_html);
    
            apex_mail.add_attachment(p_mail_id    => l_id,
                                     p_attachment => l_generated_file,
                                     p_filename   => 'Employee Details.xlsx',
                                     p_mime_type  => l_attach_mime);
            
            apex_mail.push_queue();   
     
exception WHEN others THEN
x_return_status  := 'E';
x_return_message :='Error - '||substr(1,100,sqlerrm);
END table_data_to_excel_file;

Step 3: Execute a procedure table_data_to_excel_file.

DECLARE
  x_return_status  VARCHAR2(200);
  x_return_message VARCHAR2(200);
BEGIN
  table_data_to_excel_file( x_return_status => x_return_status, 
                             x_return_message => x_return_message );
  
  dbms_output.put_line('X_RETURN_STATUS = ' || x_return_status);
  dbms_output.put_line('X_RETURN_MESSAGE = ' || x_return_message);
END;

You will get a nice mail with attachment as below,

Description:


ORA_EXCEL.new_document: Create new Excel document
ORA_EXCEL.add_sheet: Add sheet to current document
ORA_EXCEL.add_row: Add row to current sheet
ORA_EXCEL.set_cell_value: Set cell value with string value type
ORA_EXCEL.set_column_width: Set column width
ORA_EXCEL.set_cell_style: Set cell style
ORA_EXCEL.add_style: Create cell formatting style; may be applied to multiple cells.
ORA_EXCEL.set_cell_align_right: Align text Right
ORA_EXCEL.set_cell_align_left: Align text left
ORA_EXCEL.save_to_blob: Save generated Excel document to PL/SQL BLOB variable type.

APEX_MAIL.send: Send an outbound email message from an Oracle Application Express application
APEX_MAIL.add_attachment: Send an outbound email message from an Oracle Application Express application as an attachment
APEX_MAIL.push_queue: Deliver mail messages stored in APEX_MAIL_QUEUE.

Limitation: You can process up-to 1000 rows when you are using free version of ORA_EXCEL API. If you wish to process more rows then you need to get a license, for which you need to pay.

That's it.

Happy APEXing!!!...

Reference:

Comments

  1. Amit11 March 2021 at 08:16

    The google drive link is not opening. Can you fix it

    ReplyDelete
  2. Rathish Kumar22 October 2021 at 03:02

    Is ora_excel requires license? I am unable to access google drive to download ORA_EXCEL API

    ReplyDelete
    Replies
    1. Karkuvelraja Thangamariappan17 March 2022 at 05:34

      The file which I uploaded is FREE version. Kindly try now.

      Delete
  3. Prakash4 January 2022 at 02:05

    Thanks for sharing such worthy content, this information is useful for knowledge seekers. Waiting for a more upcoming post like this.
    Graphic Designer Roles
    What Can Graphic Designers Do

    ReplyDelete
  4. Niyaz5 January 2022 at 01:53

    Wonderful Blog!!! Waiting for your upcoming data... thanks for sharing with us.
    How to Start Software Testing Career
    Career in Software Testing

    ReplyDelete
  5. Marcio7 January 2022 at 05:30

    hello the download link is not working, can you send it to me please

    ReplyDelete
  6. Ainlec0crysga22 April 2022 at 21:10

    Ainlec0crysga Sean Shop https://marketplace.visualstudio.com/items?itemName=roconrade.Descargar-Weird-Hat-Fight-gratuita
    bebulllynin

    ReplyDelete
  7. OsupberQlas_ri19 May 2022 at 04:03

    OsupberQlas_ri Stacy Brown https://www.fabulously.org/ja/profile/paxtunpaxtungilberta/profile
    penramangcard

    ReplyDelete
  8. Ofirmopbiro31 May 2022 at 14:32

    Ofirmopbiro John Moreno click
    kannpopimab

    ReplyDelete
  9. dacomQhie_ri31 July 2022 at 13:57

    dacomQhie_ri Stacy Cordova click here
    click here
    download
    download
    erroracna

    ReplyDelete

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