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:
Hi ,
ReplyDeleteCan you please send me the SEND_EMAIL () procedure code which you have used for this ?