How To Write To A Path, Make A FTP Operation, Send E-Mail A SQL Query Result In Excel File Via PL/SQL

After I read some blog entries about exporting Excel files via PL/SQL recently, I also decided to share my Excel API code in PL/SQL. I wrote a similar article before .  This API has following features;

Most of the interface procedures accepts SQL query as parameter:

• Creates an excel file content as CLOB variable (PRC_CREATE_EXCEL_BY_QUERY)
• Writes any CLOB data to a specific folder that defined before with a filename. (PRC_WRITE_EXCEL_BY_MANUAL)
• Writes any SQL query to a specific folder that defined before (PRC_WRITE_EXCEL_BY_QUERY)
• Sends e-mail any CLOB data as attachment with TO, CC, BCC features and with also some specific words with spaces in sender name like “John Smith Company”. (PRC_EMAIL_EXCEL_BY_MANUAL)
• Sends e-mail with SQL query result in Excel file as attachment (PRC_EMAIL_EXCEL_BY_QUERY)
• Writes any CLOB data to a FTP path as file. (PRC_FTP_EXCEL_BY_MANUAL)
• Writes SQL query result in Excel file to a FTP path. (PRC_FTP_EXCEL_BY_QUERY)
• Downloads SQL query result in Excel file from APEX application using WPG_DOCLOAD package. (PRC_DOWNL_EXCEL_FILE)
• You can also create dynamic Excel file content in CLOB variable. For example;


DECLARE
MYEXCELCONTENT CLOB;
BEGIN
-- Test statements here
PK_EXCEL_API.G_SHOW_BORDER := 'Y';
PK_EXCEL_API.EX_EXCEL_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_WORKSHEET_OPEN(MYEXCELCONTENT, 'test');

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'asdasdasd');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'INVOICE NUMBER');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'SEQ NO');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'VERSION..');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'BLA BLA BLA');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_WORKSHEET_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.EX_EXCEL_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.PRC_WRITE_EXCEL_FILE(P_CLOB => MYEXCELCONTENT,
P_FILENAME => 'test.xls',
P_DIR => 'UTL_FILE_DIR');
END;

Here is the all package here:

PK_EXCEL_API_v1.0

PK_EXCEL_API_v1.0