Evaluate Consulting Services

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,

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;

Leave a Comment

Your email address will not be published. Required fields are marked *