Overview :
The process of generating an Excel file and sending it as an email attachment using PL/SQL packages in Oracle Apex is a powerful solution for automating the task of creating and sending reports.
Technologies and Tools Used :
The following technology has been used to achieve the same.
- SQLDeveloper
- Oracle Apex
Use Case :
Use Case: Automated Monthly Sales Performance Report
A sales department of a company wants to automate the process of generating a monthly report on their sales performance and sending it directly to their team’s email addresses with accompanying messages.
Architecture :
Steps :
- First, we need to install the package specifications and package body for “XXTH_EXPORT_XLSX”.
excel_download_package_specification
2. Next, we can develop a process that uses the package as follows:
a) Assign the sheet name by calling the function XXTH_EXPORT_XLSX.NEW_SHEET(‘test’).
b) Generate the file with a query using the function
XXTH_EXPORT_XLSX.QUERY2SHEET_WITHOUT_BLOB
(L_V, P_SHEET => 1, P_COLUMN_HEADERS => TRUE),
where “L_V” is the local variable that stores the query, P_SHEET specifies the number of sheets, and P_COLUMN_HEADERS adds column names.
3. Finally, the package XXTH_EXPORT_XLSX.FINISH is used to store the generated file in a local variable.
4. Once the file has been generated, we can use the APEX mail send package or another configured mail package to send an email with the file as an attachment. The file stored in the local variable can be passed as an attachment when sending the email.
Point 2 :
“DECLARE
LB_BLOB BLOB;
LN_COL_CNT NUMBER := 0;
LN_ROW_CNT NUMBER := 0;
LN_CHECK NUMBER := 0;
LV_MESSAGE VARCHAR2 (1000);
LV_INSTANCE VARCHAR2 (100);
L_ID NUMBER;
V_1 VARCHAR2 (1000);
V_2 VARCHAR2 (1000);
L_V VARCHAR2 (32000);
V_M1 VARCHAR2 (50);
V_M2 VARCHAR2 (50);
V_M3 VARCHAR2 (50);
V_M4 VARCHAR2 (50);
V_M5 VARCHAR2 (50);
BEGIN
SELECT A.MONTH
INTO V_1
FROM ABC_FORECAST A
WHERE UPPER (A.MONTH) = UPPER ( :P1_YEAR);
SELECT CSE.CSE_REP_ID
INTO V_2
FROM ABC_FORVIEW CSE
WHERE CSE_REP_ID = :P1_CSE;
SELECT M1 “M1”
INTO V_M1
FROM ABC_FORECAST
WHERE LOWER (MONTH) = LOWER ( :P1_YEAR);
SELECT M2 “M2”
INTO V_M2
FROM ABC_FORECAST
WHERE LOWER (MONTH) = LOWER ( :P1_YEAR);
SELECT M3 “M3”
INTO V_M3
FROM ABC_FORECAST
WHERE LOWER (MONTH) = LOWER ( :P1_YEAR);
SELECT M4 “M4”
INTO V_M4
FROM ABC_FORECAST
WHERE LOWER (MONTH) = LOWER ( :P1_YEAR);
SELECT M5 “M5”
INTO V_M5
FROM ABC_FORECAST
WHERE LOWER (MONTH) = LOWER ( :P1_YEAR);
BEGIN
L_V :=
‘SELECT DTL.CSE as “CSE”,
DTL.PLANT AS “PLANT”,
DTL.SPEC_NUMBER AS “SPEC NO”,
DTL.CODE AS “FG CODE”,
(SELECT FG_DESC
FROM ABC_SPECS
WHERE DOCNO = DTL.SPEC_NUMBER) “DESCRIPTION”,
DTL.UOM AS “UOM”,
ROUND (DTL.DISPATCH_QTY, 2) “DISPATCH QTY”,
DTL.FORECAST_M1 ‘
|| ‘”‘
|| V_M1
|| ‘”‘
|| ‘ ,
DTL.FORECAST_M2 ‘
|| ‘”‘
|| V_M2
|| ‘”‘
|| ‘,
DTL.FORECAST_M3 ‘
|| ‘”‘
|| V_M3
|| ‘”‘
|| ‘,
DTL.FORECAST_M4 ‘
|| ‘”‘
|| V_M4
|| ‘”‘
|| ‘,
DTL.FORECAST_M5 ‘
|| ‘”‘
|| V_M5
|| ‘”‘
|| ‘
FROM ABC_FORECAST MST, ABC_FORECAST_DTL DTL
WHERE MST.RECORD_ID = DTL.RECORD_ID
AND LOWER (MST.MONTH) = ”’
|| LOWER (V_1)
|| ”’
AND lower(DTL.SALES_REP_ID) = ”’
|| V_2
|| ”’
‘;
XXTH_EXPORT_XLSX.NEW_SHEET (‘test’);
XXTH_EXPORT_XLSX.QUERY2SHEET_WITHOUT_BLOB (L_V,
P_SHEET => 1,
P_COLUMN_HEADERS => TRUE);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT XXTH_EXPORT_XLSX.FINISH INTO LB_BLOB FROM DUAL;
END;
BEGIN
L_ID :=
APEX_MAIL.SEND (
P_TO => ‘xyz@gmail.com’,
P_FROM => ‘xyz123@gmail.com’,
P_BODY =>
‘Welocme to Oracle Apex Sending Emailer Session with Attachment’,
P_BODY_HTML => ‘test
‘,
P_SUBJ => ‘Welcome Emailer’);
APEX_MAIL.ADD_ATTACHMENT (
P_MAIL_ID => L_ID,
P_ATTACHMENT => LB_BLOB,
P_FILENAME => ‘rmforecast.xlsx’,
P_MIME_TYPE => ‘application/vnd.ms-excel’);
END;
END;”
