Saturday, August 9, 2008

Javita

Javita I


Java
is a coffee produced on the island of Java. In the United States, the term "Java" by itself is slang for coffee generally.

The Indonesian phrase Kopi Jawa refers not only to the origin of the coffee, but is used to distinguish the strong, black, very sweet coffee, with powdered grains in the drink, from other forms of the drink.

The Dutch began cultivation of coffee trees on Java (part of the Dutch East Indies in the 17th century and it has been exported globally since. Today Java's old colonial era plantations provide just a fraction of the coffee grown on the island.

Javita II

Java is also a programming language and according the Java FAQ, Bill Joy, currently a vice president at Sun Microsystems, is widely believed to have been the person to conceive of the idea of a programming language that later became Java. In late 1970's, Joy wanted to design a language that combined the best features of MESA and C. In an attempt to re-write the UNIX operating system in 1980's, Joy decided that C++ was inadequate for the job. A better tool was needed to write short and effective programs. It was this desire to invent a better programming tool that swayed Joy, in 1991, in the direction of Sun's "Stealth Project" - as named by Scott McNealy, Sun's president.

Members of the Stealth Project, which later became known as the Green Project, divided the tasks amongst themselves. Mike Sheradin was to focus on business development, Patrick Naughton was to begin work on the graphics system, and James Gosling was to identify the proper programming language for the project.

Mike Sheradin was to focus on business development, Patrick Naughton was to begin work on the graphics system, and James Gosling was to identify the proper programming language for the project.

Gosling who had joined Sun in 1984, had previously developed the commercially unsuccessful NeWS windowing system as well as GOSMACS - a C language implementation of GNU EMACS. He began with C++, but soon after was convinced that C++ was inadequate for this particular project. His extensions and modifications to C++ (also know as C++ ++ --), were the first steps towards the development of an independent language that would fit the project objectives. He named the language "Oak" while staring at an oak tree outside his office window! The name "Oak" was later dismissed due to a patent search which determined that the name was copyrighted and used for another programming language.

According to Gosling, "the Java development team discovered that Oak was the name of a programming language that predated Sun's language, so another name had to be chosen."It's surprisingly difficult to find a good name for a programming language, as the team discovered after many hours of brainstorming. Finally, inspiration struck one day during a trip to the local coffee shop" Gosling recalls. Others have speculated that the name Java came from several individuals involved in the project: James gosling, Arthur Van hoff, Andy bechtolsheim...

Saturday, August 2, 2008

How to send emails with attachments from PL/SQL stored procedures

I was looking for some documentation in order to implement a function that can send an email with attachments from a PL/SQL stored procedure.

Was kind of difficult to find very good documentation although this is a very simple process, specially about how to implement the function of sending some text as an attached file.

The are 2 main approaches to solve this problem:

1. UTL_SMTP: Available in Oracle 8i and 9i (for 10g use UTL_MAIL), is not easy to use to send emails with attached files but if you understand the concept of how the SMTP server handles this will be much easier. I will cover this below.

2. Using Java, easier to send attachments but difficult to install and setup (you got to have access to user SYS in order to compile and run some functions in Java and also to run some commands to be able to compile the Java procedure)

In this article I will refer to the first approach, using UTL_STMP. I will save the Java approach for a later post in this blog.

How does SMTP work to send attachments?

What the SMTP server does in order to send an email with a text file attached it's to get the text that arrives in one of the parameters or arguments passed to the function. The SMTP receives the text and encodes or decodes the text on the fly, depending if it's sending or receiving the email. So when it arrives to your computer it's when it gets 'translated' into a certain type of file.

This is the way that the process of sending a TXT attachments works. The SMTP server uses some commands to send the text and not the file itself. This means that contents when arrive to the destination will be stored as an attached file.

This the reason why when sending an attached file it's not needed to specify a path. You just pass the contents which will be encoded or decoded on the fly when receiving or sending the email...

I don't know why is that NOBODY explains this simple but very useful concept. All the sites that I have seen only bother to put the code of the functions needed to send email with attachments but nobody explains anything, the 'know-how' it's kept as a secret.

Now that you know how SMTP works let's get into action using PL/SQL

Using UTL_SMTP to send mails with attachments

UTL_SMTP, as the name itself referes, it's a UTiLity Package included by default in Oracle Database since 8i release. It has some built in functions and procedures that basically provide an interface with the SMTP protocol commands. So the idea behind it's to interact with the SMTP server initiating and mantaining a conversation to achieve the functionality of sending emails from any stored procedure defined in PL/SQL

To implement this functionality inside your PL/SQL procedures, some functions have to be called; there is no need to initialize/setup the package because it comes pre-installed in the database server. Check the functions used, the parameters are self explanatory and the variables have to be declared previously.

Note that to send the attachment there is no need to call an specific function, the action of initializing some parameters, like the file name that will contain the attached text when the email it reaches its destination, is the only thing needed by the STMP server to send an email including text as an attachment.

-- initiate the conversation with the SMTP server, establishing the connection
v_smtp_connection := utl_smtp.open_connection( v_smtp_host, v_smtp_port );

-- perform a handshake with the SMTP server
utl_smtp.helo( v_smtp_connection, v_smtp_host );

-- sender (from) address
v_sender:= user@server.com

-- set the 'from' address of the message
utl_smtp.mail( v_smtp_connection, v_sender );

-- recipient (to) address
v_sender:= user@host.com

-- set the 'to' address of the message

utl_smtp.rcpt(v_smtp_connection, v_recipient);

-- send the email, using char(13) it's a way to pass the parameters to the SMTP server
utl_smtp.data(v_smtp_connection,'Single string message.' || Chr(13));

-- close the connection
UTL_SMTP.quit(l_mail_conn);


Sample Code

This code it's to demonstrate the use of the UTL_SMTP package in a real life piece of working code that I use on a application implemented for a datamart application in an actual project that I am working on. Look that the text to be attached comes as a parameter to the function, it can hold up to 32768 characters long, if you need you can use a CLOB datatype which can handle up to 4 Gigs. There is also a place to name the file name that will hold the attached text once it reaches its destination.

The values To, Cc, Bcc are only used to display the recicpients of the email in a certain way in the email client (Hotmail, Eudora, Outlook, Yahoo, etc) . The values on this fields have nothing to do actually with the mechanism that sends the email as this fields are used only for representation purposes of how the recipients will be shown in each email client who will be used to read the email.

This function is commonly used in applications to send results from queries and it's usually used inside a for cursor loop.

Function send_mail(
v_recipient varchar2, -- Field To of the email
v_subject varchar2, -- Subject of the email
v_body varchar2, -- Body of the email
v_attachment varchar2, -- Text that will be sent as an attached file
v_error_msg out varchar2) -- To hold the errors in the exception section

return boolean is

-- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;

-- variable to hold the smtp host name
v_smtp_host varchar2(100) default 'my_smtp.com';

-- variable to hold the smtp port
v_smtp_port number default 25;

-- variable to hold the sender, from field
v_sender varchar2(100) default 'user@host.com';

begin

-- establish the connection to the smtp server
v_smtp_connection := utl_smtp.open_connection(v_smtp_host, v_smtp_port);

-- perform a handshake with the smtp server
utl_smtp.helo(v_smtp_connection, v_smtp_host);

-- set the 'from' address of the message
utl_smtp.mail(v_smtp_connection, v_sender);

-- add the recipient to the message
utl_smtp.rcpt(v_smtp_connection, v_recipient);

-- send the email
utl_smtp.data(v_smtp_connection,
'Date: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.crlf ||
'From: ' || v_sender || utl_tcp.crlf ||
'Subject: '|| v_subject || utl_tcp.crlf ||
'To: ' || v_recipient || utl_tcp.crlf ||
'Cc: ' || v_cc || utl_tcp.crlf ||

'MIME-Version: 1.0'|| utl_tcp.crlf || -- use Mime mail standard
'Content-Type: multipart/mixed;' || utl_tcp.crlf ||
' boundary="-----SECBOUND"'|| utl_tcp.crlf ||
utl_tcp.crlf ||

'-------SECBOUND'|| utl_tcp.crlf ||
'Content-Type: text/html;'|| utl_tcp.crlf ||
'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf ||
utl_tcp.crlf ||
v_body || utl_tcp.crlf || -- message body
utl_tcp.crlf ||

'-------SECBOUND'|| utl_tcp.crlf ||
'Content-Type: text/plain;' || utl_tcp.crlf ||
' name="error.log"'|| utl_tcp.crlf || -- file name that will hold the attached text
'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf ||
'Content-Disposition: attachment;'|| utl_tcp.crlf ||
' filename="error.log"'|| utl_tcp.crlf ||
utl_tcp.crlf ||
v_attachment || utl_tcp.crlf || -- attachment
utl_tcp.crlf ||

'-------SECBOUND--' -- end mime mail
);

-- end the connection to the smtp server
utl_smtp.quit(v_smtp_connection);

return true;

exception
when utl_smtp.invalid_operation then
v_error_msg := ' Invalid Operation in Mail attempt using UTL_SMTP.';
return false;
when utl_smtp.transient_error then
v_error_msg := ' Temporary e-mail issue - try again';
return false;
when utl_smtp.permanent_error then
v_error_msg := ' Permanent Error Encountered.';
return false;

end send_mail;

If you need any clarifications or you have found some errors in the source code please send me an email to: gongorac@gmail.com or drop me a note below.