Generic Dynamic Mail PL/SQL Procedure with Dynamic Query, Body and CSV Attachment
CREATE OR REPLACE PROCEDURE FMS_ADMIN.SEND_DYNAMIC_MAIL_WITH_ATTACHMENT
(
P_TO_MAIL IN VARCHAR2,
P_FROM_MAIL IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_BODY IN CLOB,
P_QUERY IN CLOB,
P_FILE_NAME IN VARCHAR2 DEFAULT 'attachment.csv',
P_SMTP_HOST IN VARCHAR2 DEFAULT '10.195.255.255',
P_SMTP_PORT IN NUMBER DEFAULT 123
)
IS
------------------------------------------------------------------
-- SMTP VARIABLES
------------------------------------------------------------------
L_MAIL_CONN UTL_SMTP.CONNECTION;
L_BOUNDARY VARCHAR2(100) := '----=*#abc1234321#*=';
------------------------------------------------------------------
-- CSV VARIABLES
------------------------------------------------------------------
L_CLOB CLOB;
L_LEN NUMBER;
L_POS NUMBER := 1;
L_STEP NUMBER := 1900;
------------------------------------------------------------------
-- DBMS_SQL VARIABLES
------------------------------------------------------------------
L_CURSOR INTEGER;
L_COL_CNT NUMBER;
L_DESC_TAB DBMS_SQL.DESC_TAB;
L_COLUMN_VALUE VARCHAR2(4000);
L_STATUS NUMBER;
L_HEADER VARCHAR2(32767);
L_LINE VARCHAR2(32767);
BEGIN
------------------------------------------------------------------
-- CREATE TEMP CLOB
------------------------------------------------------------------
DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
------------------------------------------------------------------
-- OPEN CURSOR
------------------------------------------------------------------
L_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(
L_CURSOR,
P_QUERY,
DBMS_SQL.NATIVE
);
------------------------------------------------------------------
-- DESCRIBE COLUMNS
------------------------------------------------------------------
DBMS_SQL.DESCRIBE_COLUMNS
(
L_CURSOR,
L_COL_CNT,
L_DESC_TAB
);
------------------------------------------------------------------
-- DEFINE COLUMNS
------------------------------------------------------------------
FOR I IN 1 .. L_COL_CNT
LOOP
DBMS_SQL.DEFINE_COLUMN
(
L_CURSOR,
I,
L_COLUMN_VALUE,
4000
);
IF I > 1 THEN
L_HEADER := L_HEADER || ',';
END IF;
L_HEADER := L_HEADER || L_DESC_TAB(I).COL_NAME;
END LOOP;
------------------------------------------------------------------
-- WRITE CSV HEADER
------------------------------------------------------------------
DBMS_LOB.WRITEAPPEND
(
L_CLOB,
LENGTH(L_HEADER || CHR(10)),
L_HEADER || CHR(10)
);
------------------------------------------------------------------
-- EXECUTE QUERY
------------------------------------------------------------------
L_STATUS := DBMS_SQL.EXECUTE(L_CURSOR);
------------------------------------------------------------------
-- FETCH ROWS
------------------------------------------------------------------
WHILE DBMS_SQL.FETCH_ROWS(L_CURSOR) > 0
LOOP
L_LINE := NULL;
FOR I IN 1 .. L_COL_CNT
LOOP
DBMS_SQL.COLUMN_VALUE
(
L_CURSOR,
I,
L_COLUMN_VALUE
);
------------------------------------------------------------------
-- HANDLE SPECIAL CHARACTERS
------------------------------------------------------------------
L_COLUMN_VALUE := REPLACE(L_COLUMN_VALUE, '"', '""');
IF I > 1 THEN
L_LINE := L_LINE || ',';
END IF;
L_LINE := L_LINE || '"' || NVL(L_COLUMN_VALUE, '') || '"';
END LOOP;
DBMS_LOB.WRITEAPPEND
(
L_CLOB,
LENGTH(L_LINE || CHR(10)),
L_LINE || CHR(10)
);
END LOOP;
------------------------------------------------------------------
-- CLOSE CURSOR
------------------------------------------------------------------
DBMS_SQL.CLOSE_CURSOR(L_CURSOR);
------------------------------------------------------------------
-- OPEN SMTP CONNECTION
------------------------------------------------------------------
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION
(
P_SMTP_HOST,
P_SMTP_PORT
);
UTL_SMTP.HELO
(
L_MAIL_CONN,
P_SMTP_HOST
);
------------------------------------------------------------------
-- MAIL FROM
------------------------------------------------------------------
UTL_SMTP.MAIL
(
L_MAIL_CONN,
P_FROM_MAIL
);
------------------------------------------------------------------
-- MULTIPLE RECIPIENTS
------------------------------------------------------------------
FOR X IN
(
SELECT REGEXP_SUBSTR
(
P_TO_MAIL,
'[^,;]+',
1,
LEVEL
) EMAIL_ID
FROM DUAL
CONNECT BY REGEXP_SUBSTR
(
P_TO_MAIL,
'[^,;]+',
1,
LEVEL
) IS NOT NULL
)
LOOP
UTL_SMTP.RCPT
(
L_MAIL_CONN,
TRIM(X.EMAIL_ID)
);
END LOOP;
------------------------------------------------------------------
-- OPEN DATA
------------------------------------------------------------------
UTL_SMTP.OPEN_DATA(L_MAIL_CONN);
------------------------------------------------------------------
-- MAIL HEADERS
------------------------------------------------------------------
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Date: ' ||
TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') ||
UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'To: ' || P_TO_MAIL || UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'From: ' || P_FROM_MAIL || UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Subject: ' || P_SUBJECT || UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'MIME-Version: 1.0' ||
UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Content-Type: multipart/mixed; boundary="' ||
L_BOUNDARY || '"' ||
UTL_TCP.CRLF ||
UTL_TCP.CRLF
);
------------------------------------------------------------------
-- HTML BODY PART
------------------------------------------------------------------
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'--' || L_BOUNDARY || UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Content-Type: text/html; charset=UTF-8' ||
UTL_TCP.CRLF ||
UTL_TCP.CRLF
);
------------------------------------------------------------------
-- DYNAMIC HTML BODY
------------------------------------------------------------------
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'<html>' ||
'<body style="font-family:Arial;">' ||
'<table width="100%" cellpadding="5" cellspacing="0" ' ||
'style="border-bottom:2px solid #004A99;">' ||
'<tr>' ||
'<td width="120">' ||
'<img src="https://upload.wikimedia.org/wikipedia/en/thumb/3/3e/BSNL_Logo.svg/1200px-BSNL_Logo.svg.png" ' ||
'width="100" height="100">' ||
'</td>' ||
'<td>' ||
'<h2 style="color:#004A99;">BSNL Franchise Management System</h2>' ||
'<p style="font-size:14px;color:#333333;">' ||
'Automated Notification from FMS' ||
'</p>' ||
'</td>' ||
'</tr>' ||
'</table>' ||
'<br>' ||
P_BODY ||
'<br><br>' ||
'<table border="1" cellpadding="5" cellspacing="0" ' ||
'style="border-collapse:collapse;font-size:13px;">' ||
'<tr style="background-color:#004A99;color:white;">' ||
'<th>Date</th>' ||
'<th>Attachment</th>' ||
'</tr>' ||
'<tr>' ||
'<td>' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') || '</td>' ||
'<td>' || P_FILE_NAME || '</td>' ||
'</tr>' ||
'</table>' ||
'<br><br>' ||
'<p style="font-size:12px;color:gray;">' ||
'This is a system generated email from BSNL FMS.' ||
'</p>' ||
'</body>' ||
'</html>' ||
UTL_TCP.CRLF ||
UTL_TCP.CRLF
);
------------------------------------------------------------------
-- ATTACHMENT HEADER
------------------------------------------------------------------
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'--' || L_BOUNDARY || UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Content-Type: text/csv; name="' ||
P_FILE_NAME || '"' ||
UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Content-Disposition: attachment; filename="' ||
P_FILE_NAME || '"' ||
UTL_TCP.CRLF
);
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
'Content-Transfer-Encoding: 8bit' ||
UTL_TCP.CRLF ||
UTL_TCP.CRLF
);
------------------------------------------------------------------
-- WRITE CSV DATA
------------------------------------------------------------------
L_LEN := DBMS_LOB.GETLENGTH(L_CLOB);
WHILE L_POS <= L_LEN
LOOP
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
DBMS_LOB.SUBSTR
(
L_CLOB,
L_STEP,
L_POS
)
);
L_POS := L_POS + L_STEP;
END LOOP;
------------------------------------------------------------------
-- END MIME
------------------------------------------------------------------
UTL_SMTP.WRITE_DATA
(
L_MAIL_CONN,
UTL_TCP.CRLF ||
'--' || L_BOUNDARY || '--' ||
UTL_TCP.CRLF
);
------------------------------------------------------------------
-- CLOSE MAIL
------------------------------------------------------------------
UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
UTL_SMTP.QUIT(L_MAIL_CONN);
------------------------------------------------------------------
-- FREE TEMP CLOB
------------------------------------------------------------------
IF DBMS_LOB.ISTEMPORARY(L_CLOB) = 1 THEN
DBMS_LOB.FREETEMPORARY(L_CLOB);
END IF;
EXCEPTION
WHEN OTHERS THEN
------------------------------------------------------------------
-- CLOSE SMTP CONNECTION
------------------------------------------------------------------
BEGIN
UTL_SMTP.QUIT(L_MAIL_CONN);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
------------------------------------------------------------------
-- CLOSE CURSOR
------------------------------------------------------------------
BEGIN
IF DBMS_SQL.IS_OPEN(L_CURSOR) THEN
DBMS_SQL.CLOSE_CURSOR(L_CURSOR);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
------------------------------------------------------------------
-- FREE CLOB
------------------------------------------------------------------
BEGIN
IF DBMS_LOB.ISTEMPORARY(L_CLOB) = 1 THEN
DBMS_LOB.FREETEMPORARY(L_CLOB);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE_APPLICATION_ERROR
(
-20001,
'Error in SEND_DYNAMIC_MAIL_WITH_ATTACHMENT : ' || SQLERRM
);
END;
/
Sample Usage
BEGIN
FMS_ADMIN.SEND_DYNAMIC_MAIL_WITH_ATTACHMENT
(
P_TO_MAIL => 'abc@bsnl.co.in,xyz@bsnl.co.in',
P_FROM_MAIL => 'bsnlfms@cdr.bsnl.co.in',
P_SUBJECT => 'Skypro Voluntary Closures',
P_BODY =>
'<p>Please find attached voluntary closure details.</p>' ||
'<p>Kindly process all disconnections immediately.</p>',
P_QUERY =>
'SELECT
ORDER_NO,
CIRCLE,
PHONE_NO,
CUST_ACCNT_NO,
MOBILE,
SUBSCRIBER_ID
FROM IFTV_VOLUNTARY_CLOSURES
WHERE SENT_TO_SKYPRO IS NULL',
P_FILE_NAME => 'skypro_closures.csv'
);
END;
/
Features
Fully dynamic SQL query
Automatically generates CSV headers
Dynamic HTML email body
Supports multiple recipients
Generic reusable utility procedure
Handles any table/query
Proper exception handling
CSV special character handling
Dynamic attachment filename
SMTP configurable
HTML formatted mail
No hardcoded columns
The procedure SEND_DYNAMIC_MAIL_WITH_ATTACHMENT is a generic Oracle PL/SQL utility to:
Execute any SQL query dynamically
Convert query output into CSV format
Attach the CSV to an email
Send a formatted HTML email using SMTP
It is designed as a reusable framework instead of writing separate mail procedures for each report.
Procedure Purpose
This procedure automates report mailing from Oracle database.
Typical use cases:
Daily reports
Fault reports
Invoice reports
Pending orders
Customer lists
Audit extracts
Exception reports
Input Parameters
| Parameter | Purpose |
|---|---|
P_TO_MAIL | Recipient email IDs |
P_FROM_MAIL | Sender email ID |
P_SUBJECT | Email subject |
P_BODY | Dynamic HTML/text body |
P_QUERY | SQL query whose output becomes CSV |
P_FILE_NAME | Attachment filename |
P_SMTP_HOST | SMTP server IP |
P_SMTP_PORT | SMTP port |
High Level Flow
INPUT QUERY
↓
EXECUTE QUERY DYNAMICALLY
↓
FETCH COLUMN NAMES
↓
CREATE CSV HEADER
↓
FETCH ROWS
↓
GENERATE CSV CONTENT
↓
OPEN SMTP CONNECTION
↓
BUILD HTML EMAIL
↓
ATTACH CSV
↓
SEND EMAIL
↓
CLEANUP
Step-by-Step Explanation
1. SMTP Variables
L_MAIL_CONN UTL_SMTP.CONNECTION;
Stores SMTP session connection.
2. MIME Boundary
L_BOUNDARY VARCHAR2(100) := '----=*#abc1234321#*=';
Used to separate:
HTML body
Attachment
inside one email.
Email internally looks like:
BODY PART
---------
ATTACHMENT PART
Boundary marks those sections.
3. Temporary CLOB
L_CLOB CLOB;
Stores entire CSV content.
Because report size can be large.
4. Dynamic SQL Engine
DBMS_SQL
This is the most important part.
It allows execution of dynamic queries.
Example:
SELECT * FROM EMP
or
SELECT ORDER_NO, PHONE_NO FROM XYZ
without hardcoding columns.
5. Parse Dynamic Query
DBMS_SQL.PARSE
(
L_CURSOR,
P_QUERY,
DBMS_SQL.NATIVE
);
Oracle parses the query supplied in parameter.
6. Describe Columns Dynamically
DBMS_SQL.DESCRIBE_COLUMNS
This reads:
number of columns
column names
datatype metadata
Example:
ORDER_NO
PHONE_NO
STATUS
This is how CSV headers are generated automatically.
7. Define Columns
DBMS_SQL.DEFINE_COLUMN
Allocates memory for fetching column values.
Without this, Oracle cannot retrieve row values.
8. Generate CSV Header
L_HEADER := L_HEADER || L_DESC_TAB(I).COL_NAME;
Builds:
ORDER_NO,PHONE_NO,STATUS
automatically.
9. Execute Query
L_STATUS := DBMS_SQL.EXECUTE(L_CURSOR);
Runs the SQL query.
10. Fetch Rows
WHILE DBMS_SQL.FETCH_ROWS(L_CURSOR) > 0
Reads one row at a time.
11. Fetch Column Values
DBMS_SQL.COLUMN_VALUE
Gets each column value dynamically.
Example:
1001
9848012345
ACTIVE
12. CSV Special Character Handling
REPLACE(L_COLUMN_VALUE, '"', '""')
Escapes double quotes.
Important for valid CSV format.
Example:
A "TEST"
becomes
"A ""TEST"""
13. Build CSV Line
L_LINE := L_LINE || '"' || VALUE || '"';
Creates safe CSV rows.
Example:
"1001","9848012345","ACTIVE"
14. Write to CLOB
DBMS_LOB.WRITEAPPEND
Appends data into large CSV memory object.
15. SMTP Connection
UTL_SMTP.OPEN_CONNECTION
Connects to mail server.
Example:
10.195.216.57:587
16. Multiple Recipients
REGEXP_SUBSTR
Splits:
a@x.com,b@y.com
into separate email IDs.
17. MIME Email Headers
Content-Type: multipart/mixed
Tells mail client:
This email contains attachment(s)
18. HTML Body
The procedure creates professional HTML mail.
Includes:
BSNL header
Logo
Dynamic message
Attachment details
Footer
19. Attachment Section
Content-Disposition: attachment
Tells email client:
Download this as file
20. Write CSV Attachment
DBMS_LOB.SUBSTR
Sends CSV in chunks.
Necessary because SMTP cannot send huge data in one write.
21. End MIME
--boundary--
Marks end of email content.
22. Cleanup
The procedure safely closes:
SMTP connection
Cursor
Temporary CLOB
Prevents memory leaks.
23. Exception Handling
WHEN OTHERS
Ensures cleanup happens even if:
SMTP fails
Query fails
Network issue occurs
Then raises readable error.
Why This Procedure Is Powerful
Earlier Approach
You had:
one procedure per report
hardcoded columns
hardcoded table
hardcoded CSV logic
Very difficult to maintain.
New Generic Approach
Now only this changes:
P_QUERY
P_BODY
P_SUBJECT
P_FILE_NAME
Everything else is reusable.
Example
Input Query
SELECT EMPNO, ENAME, SAL
FROM EMP
Automatically Generated CSV
EMPNO,ENAME,SAL
7369,SMITH,800
7499,ALLEN,1600
Advantages
| Feature | Benefit |
|---|---|
| Dynamic query | Reusable |
| Auto CSV headers | No coding needed |
| HTML mail | Professional |
| Generic utility | Single framework |
| Multiple recipients | Flexible |
| CLOB handling | Large reports |
| Exception handling | Stable |
| CSV escaping | Correct format |
Recommended Enhancements
Future improvements possible:
ZIP attachment
XLSX attachment
CC/BCC support
Inline table preview
Password protected attachment
Scheduler integration
Logging table
Retry mechanism
SMTP authentication
TLS/SSL support
Tags:
Oracle