RE: Off-Topic: How to send email from PL/SQL

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Thu, 14 Dec 2000 09:56:47 -0500
Message-Id: <10710.124569@fatcity.com>

We just implemented this. It works great. Here's a doc that I used to get started. Credit to the author. Also check out Metalink articles 106513.1 and 106436.1.

Steps for enabling email functionality from the Oracle Database: I.Install the required Java Classes:
The required Java classes are automatically installed during the typical installation of the Oracle Software, however, they are not installed during custom installation. To install the required Java classes, perform the following commands:
Connect as SYS User.
Run %ORACLE_HOME/javavm/plsql/jlib/initjvm.sql Run %ORACLE_HOME/rdbms/java/install/initplsj.sql

II.Configure the SMTP server:
Get the SMTP server configured, the Unix Admin can help you with the configurations.

III.Write the PL/SQL Code:
Your code will loom like this:

CREATE OR REPLACE PROCEDURE send_mail

(  sender     IN VARCHAR2,
   recipient  IN VARCHAR2,
   subject    IN VARCHAR2,

message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp01.us.oracle.com'; mail_conn utl_smtp.connection;

crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 ); mesg VARCHAR2( 1000 );
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);

  mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
         'From:  <'||sender||'>' || crlf ||
         'Subject: '||subject || crlf ||
         'To: '||recipient || crlf ||
         '' || crlf || message;
  utl_smtp.helo(mail_conn, mailhost);
  utl_smtp.mail(mail_conn, sender);
  utl_smtp.rcpt(mail_conn, recipient);

utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END; 'Mailhost' is the name of the remote host when connection is established. The port number on which SMTP server is listening is usually 25.

Open_connection(): Allows open connection to an SMTP server. Helo(): Performs initial handshaking with SMTP server after connecting. Mail(): Initiates a mail transaction with the server. The destination is a mailbox.

Rcpt():  Specifies the recipient of an email message.
Data():  Specifies the body of an email message.
Quit():  Terminates an SMTP session and disconnects from the server

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of CHAN Chor
> Ling Catherine (CSC)
> Sent: Thursday, December 14, 2000 5:36 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Off-Topic: How to send email from PL/SQL
>
>
> Hi Arun,
>
> The PL/SQL code is below. It does not seem to be able to cc email.
>
> Qn : Has anyone successfully sent and cc emails in PL/SQL ? Please help.
> Thanks.
>
> Regds,
> Chorling
>
> DECLARE
> c utl_smtp.connection;
>
>
>
> PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
>
> BEGIN
>
> utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
>
> END;
>
>
>
> BEGIN
>
> c := utl_smtp.open_connection('smtp-server.acme.com'); -- your
> smtp server
> utl_smtp.helo(c, 'foo.com'); -- your domain
>
> utl_smtp.mail(c, 'sender_at_foo.com');
>
> utl_smtp.rcpt(c, 'recipient_at_foo.com');
>
> utl_smtp.open_data(c);
>
> send_header('From', '"Sender" <sender_at_foo.com>');
>
> send_header('To', '"Recipient" <recipient_at_foo.com>');
>
> send_header('Subject', 'Hello');
>
> utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');
>
> utl_smtp.close_data(c);
>
> utl_smtp.quit(c);
>
> EXCEPTION
>
> WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
>
> utl_smtp.quit(c);
>
> raise_application_error(-20000,
>
> 'Failed to send mail due to the following error: ' || sqlerrm);
>
> END;
>
>
>
> -----Original Message-----
> From: arun_at_ssind.stpn.soft.net
> [mailto:arun_at_ssind.stpn.soft.net]
> Sent: Thursday, December 14, 2000 6:11 PM
>
> Hi,
>
> Can you send the details how you implemented this.
>
> Thanks and Regards
> Arun
>
> ----- Original Message -----
> From: "CHAN Chor Ling Catherine (CSC)" < >
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Thursday, 14 December, 2000 01:31 PM
> Subject: Re: Off-Topic: How to send email from PL/SQL
>
>
> > Hi
> >
> > I've tried using the package utl_smtp. It works.
> >
> > Regds,
> > ChorLing
> > -----Original Message-----
> > From: CHAN Chor Ling Catherine (CSC)
> > Sent: Thursday, December 14, 2000 3:26 PM
> > To: 'ORACLE-L_at_fatcity.com'
> > Subject: Off-Topic: How to send email from PL/SQL
> >
> > Hi,
> >
> > Is it possible to send email in PL/SQL ? Is there any
> Oracle function that
> > send email in PL/SQL ? If not, I guess I've to write the
> email information
> > into a table, use a C-program to extract the information
> in the table and
> > email it to the sender. Any advice ? Thanks in advance.
> >
> > Regds,
> > Chorling
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: CHAN Chor Ling Catherine (CSC)
> > INET: clchan_at_nie.edu.sg
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be removed from).
> You may
> > also send the HELP command for other information (like
> subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: CHAN Chor Ling Catherine (CSC)
> INET: clchan_at_nie.edu.sg
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Thu Dec 14 2000 - 08:56:47 CST