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
 

Turkish Character Problem (Türkçe Karakter Problemi) February 23, 2009

When we write jsp pages, we add the following code to the page to encode the characters in desired charset.

<%@ page contentType="text/html;charset=iso-8859-9"%>

Although to add this code, we sometimes face up the character problem.  Expecially, if we change the mimeType of the page. I mean, export the page as excel.

(If you want to export any data in a excel file from jsp, change the mimetpye of the jsp and write your data in table tags)

if (pageType != null && pageType.equalsIgnoreCase("excel")) {
   String mimeType = "application/vnd.ms-excel;charset=ISO-8859-9";
   response.setContentType(mimeType);
   String filename = "MyExcelFile";
   response.setHeader("Content-Disposition","attachment;filename=" + filename + ".xls");
   //System.out.println(response.getCharacterEncoding());
}

When you get this problem, try to add the following code in the HEAD tag of the page. It will fixed up.

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-9">

 I searched this simple code for a long time:), because it think that the first code enough to encode the charset. But it is not.

 

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…)