Akdora’s Blog

Programming, Oracle, Life, Fun

Sending mail with CLOB attachment August 13, 2009

Filed under: Oracle,PL/SQL — Akdora @ 6:15 am
Tags: , , , , , , , , , ,

I needed to send an e-mail with Excel file attachment. I realize that there is not enough documentation about sending mail in CLOB filetype attachment.

Here is our procedure:

CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
(
 MSG_FROM     VARCHAR2,
 MSG_TO       VARCHAR2,
 MSG_SUBJECT  VARCHAR2,
 MSG_TEXT     VARCHAR2,
 MSG_ATT      CLOB,
 ATT_FILENAME VARCHAR2
) IS

 V_MAILHOST VARCHAR2(50) := 'mailhost';
 V_PORT     NUMBER(2) := 25;
 V_HELO     VARCHAR2(50) := 'localhost';
 (more...)
Advertisements
 

How to write,create Excel files via PL/SQL and save the file to a directory? February 6, 2009

In this post, I will write a PL/SQL package that helps us to write a EXCEL file in XML format. Then, we will save it some directory. This is a basic way to create an Excel file. There is no formatting type in the cells. We will just write something in the cells. I will try to develop this package with the formatting commands. Anyway, before we write our package, we shoul know about something oracle’s directory policy. We should tell to ORACLE, I have a directory path and I will name it. I mean, we will use create directory command of oracle.

 CREATE OR REPLACE DIRECTORY MY_ORACLE_DIR AS 'C:\oracle_test\';

To run this command, we have the following privileges;

 GRANT CREATE ANY DIRECTORY TO HR; >>>>>HR is the schema name!

On the other hand, you created an directory in another schema. You should grant  the directory to your shema.

GRANT read, write on DIRECTORY MY_ORACLE_DIR to HR;

After defining the directory to oracle, we can write our package. I passed a variable referencing the XML body of the file as CLOB in the procedures.

CREATE OR REPLACE PACKAGE pkg_excel_export IS
/**
 *  @author  : Özay AKDORA
 *  @version : 1.0
 *
 *  Name of the Application         :  pkg_excel_export.sql
 *  Creation/Modification History   :  5-Jan-2009
 *
 *  Overview of  Package/Sample     :Create Excel files via PL/SQL
 *           write the file to a directory
 * 
 **/
     PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE worksheet_open
     (
          l_xml_body      IN OUT NOCOPY CLOB,
          p_worksheetname IN VARCHAR2
     );
     PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE cell_write
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_content  IN VARCHAR2
     );
     PROCEDURE excel_get
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_filename IN VARCHAR2
     );
     PROCEDURE prc_write_file
     (
          p_filename IN VARCHAR2,
          p_dir      IN VARCHAR2,
          p_clob     IN CLOB
     );
END pkg_excel_export;
/

(more…)