Skip to main content

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

Here I am giving an example to send an oracle database table data in csv/text file as mail attachment using a simple PL/SQL APIs.

Step 1: Create a new procedure as below, which should take care of following actions,

  •  Convert table data into csv file.
  •  Save generated csv file to Oracle Directory.
  •  Send generated csv file as an attachment.
CREATE OR REPLACE 
PROCEDURE table_data_to_csv_file(p_from IN VARCHAR2,
                                                                p_to   IN VARCHAR2,
                                                                p_cc   IN VARCHAR2,
                                                                p_bcc  IN VARCHAR2,
                                                                p_file_location IN VARCHAR2,
                                                                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_mime_type       VARCHAR2 (100) := 'text/plain';
   l_body_html        CLOB;
   l_body                 CLOB;
   v_file                   utl_file.file_type;
BEGIN
  x_return_status      := 'S'; 
  x_return_message := 'Process Completed';
  
  /* To open a file for input or output */
  
  v_file := utl_file.fopen (location        => p_file_location, -- Oracle directory name
                                         filename       => 'Employee Details.csv', -- To generate text file use .txt  as the file extension
                                        open_mode   => 'w',
                                        max_linesize => 32767);
 
 /* Construct field headers */
 
  utl_file.put_line(v_file,
                      'EMPNO'              || ',' ||
                      'ENAME'              || ',' ||
                      'JOB'                     || ',' ||
                      'DEPARTMENT'  || ',' ||
                      'LOCATION'        || ',' ||
                      'MANAGER'        || ',' ||
                      'HIREDATE'         || ',' ||
                      'SALARY'             || ',' ||
                      'COMMISSION');
                      
  /* Construct data for each rows */
  
  FOR emp_data IN cur_emp_data loop
    utl_file.put_line(v_file,
                      emp_data.empno            || ',' ||
                      emp_data.ename             || ',' ||
                      emp_data.JOB                || ',' ||
                      emp_data.department     || ',' ||
                      emp_data.LOCATION   || ',' ||
                      emp_data.manager         || ',' ||
                      emp_data.hiredate          || ',' ||
                      emp_data.salary             || ',' ||
                      emp_data.commission);
  END loop;
  
  utl_file.fclose(v_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 */
    
          send_mail ( p_sender            => p_from,
                               p_recipient        => p_to,
                               p_cc                   => p_cc,
                               p_bcc                 => p_bcc,
                               p_subject           => 'Employee Details ['||to_date(SYSDATE,'DD-Mon-RRRR')||']',
                               p_filename        => 'Employee Details.csv', --  File must be there in Oracle Directory
                               p_text_msg        => l_body_html,
                               p_attach_mime  => l_mime_type
                              );
     
exception WHEN others THEN
x_return_status  := 'E';
x_return_message :='Error - '||substr(1,100,sqlerrm);
END table_data_to_csv_file;

Step 3: Execute a procedure table_data_to_csv_file.

SET serveroutput ON;

DECLARE
  p_from                  VARCHAR2(200);
  p_to                       VARCHAR2(200);
  p_cc                      VARCHAR2(200);
  p_bcc                    VARCHAR2(200);
  p_file_location     VARCHAR2(200);
  x_return_status     VARCHAR2(200);
  x_return_message VARCHAR2(200);
BEGIN
  p_from   := 'fyi@yourorganization.com';
  p_to        := 'xyz@zzz.com';
  p_cc       := 'abc@yyy.com';
  p_bcc     := 'zyx@abc.com';
  p_file_location  := 'APEXUAT'; -- Put your oracle directory name here

  table_data_to_csv_file(p_from => p_from,
                                         p_to => p_to,
                                         p_cc => p_cc,
                                         p_bcc => p_bcc,
                                         p_file_location  => p_file_location,            
                                         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,

That's it.

Happy APEXing!!!...

Reference:

Comments

  1. Hi ,
    Can you please send me the SEND_EMAIL () procedure code which you have used for this ?

    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

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,  

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