Re: off topic - E-mail from DB

From: Paul Drake <paled_at_home.com>
Date: Tue, 17 Oct 2000 01:02:12 -0400
Message-Id: <10651.119417@fatcity.com>

This is a multi-part message in MIME format.

--------------A2CE5A0F804B70F66B6BA0FA
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Oracle 8.1.6 on NT
utl_smtp.sql

have fun.

prem_at_ibsplc.com wrote:

> 
> Does anyone tell me is it possible to send e-mails from the Oracle
> database, If so how?
> we are using Oracle8.0 for the development.
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: prem_at_ibsplc.com
> 
> 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).

--------------A2CE5A0F804B70F66B6BA0FA
Content-Type: text/plain; charset=us-ascii; name="utlsmtp.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="utlsmtp.sql"

REM Copyright (c) 1999 by Oracle Corporation. REM
REM NAME
REM utlsmtp.sql - PL/SQL Package for Simple Mail Transfer Protocol (SMTP)

REM                  communication (Package Specification of UTL_SMTP)

REM
REM DESCRIPTION
REM Routines to perform SMTP communication. REM
REM NOTES
REM The procedural option is needed to use this package. REM This package must be created under SYS. REM

REM  MODIFIED (MM/DD/YY)
REM  jmuller   09/16/99 - Fix bug 708690: final pass
REM  rpang     08/12/99 - Added exception codes
REM  rpang     08/12/99 - Fixed comments
REM  rpang     07/27/99 - Added INVALID_OPERATION exception
REM  rpang     07/23/99 - Modified datatype of port number to PLS_INTEGER
REM  rpang     05/19/99 - Created

CREATE OR REPLACE PACKAGE utl_smtp AS

 /*******************************************************************

OVERVIEW *
This package provides SMTP client-side access functionality in PL/SQL.
With this package, a PL/SQL program can send electronic mails via SMTP.
This package does not allow the PL/SQL program to receive e-mails via
SMTP. The user of this package should be familiar with the SMTP protocol
as defined in RFC 821 and RFC 1869. *
This package is meant to provide an API to SMTP protocol directly. Users
may find it useful to define additional helper routines to encapsulate
the interaction with a SMTP server. *
USES *
A SMTP connection is initiated by a call to open_connection, which
returns a SMTP connection. After a connection is established, the
following calls are required to send a mail: *
helo() - identify the domain of the sender
mail() - start a mail, specify the sender
rcpt() - specify the recipient
open_data() - start the mail body
write_data() - write the mail body (multiple calls allowed)
close_data() - close the mail body and send the mail *
The SMTP connection is closed by calling quit(). *
A note on API style and raising PL/SQL exception: *
Most of the API has a function form and a procedure form. The function
form returns the reply message after the command is sent, in the form
of "XXX <an optional reply message>", where XXX is the reply code.
The procedure form of the same API calls the function form of the API,
checks the reply code and raises transient_error or permanent_error
exception if the reply code is in 400 or 500 range. The function form
of the API does not raise either of the 2 exceptions.
*
All API may raise invalid_operation exception if it is called in either
of the situations: *
1. calling API other than write_data(), write_raw_data() or close_data()
after open_data(0 is called, or
2. calling write_data(), write_raw_data() or close_data() without
first calling open_data() *
EXAMPLES
Retrieve the home page from http://www.acme.com/ *
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');
utl_smtp.helo(c, 'foo.com');
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; */

  /*
   * SMTP connection type
   */
  TYPE connection IS RECORD (
    host             VARCHAR2(255),       -- Host name of SMTP server
    port             PLS_INTEGER,         -- Port number of SMTP server
    private_tcp_con  utl_tcp.connection,  -- For internal use only
    private_state    PLS_INTEGER          -- For internal use only

);

  /*
   * SMTP reply structure
   */
  TYPE reply IS RECORD (
    code     PLS_INTEGER,                 -- 3-digit reply code
    text     VARCHAR2(508)                -- reply text

);

multiple reply lines TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;

  /*
   * Exceptions
   */
  invalid_operation EXCEPTION;  -- Operation is invalid

transient_error EXCEPTION; -- Transient server error in 400 range permanent_error EXCEPTION; -- Permanent server error in 500 range

  invalid_operation_errcode CONSTANT PLS_INTEGER:= -20000;
  transient_error_errcode   CONSTANT PLS_INTEGER:= -20001;
  permanent_error_errcode   CONSTANT PLS_INTEGER:= -20002;
  PRAGMA EXCEPTION_INIT(invalid_operation, -20000);

PRAGMA EXCEPTION_INIT(transient_error, -20001); PRAGMA EXCEPTION_INIT(permanent_error, -20002);

  /**
   * Opens a SMTP connection to a SMTP server.  When connection is made
   * succesfully, the SMTP host name and port number will be stored in
   * the connection.
   *
   * PARAMETERS
   *   host   SMTP host name to connect to
   *   port   port number of the SMTP server to connect to
   *   c      SMTP connection (OUT)
   * RETURN
   *   SMTP connection when connection is established, or
   *   the SMTP reply (welcome) message
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */
  FUNCTION open_connection(host  IN  VARCHAR2,
                           port  IN  PLS_INTEGER DEFAULT 25,
                           c     OUT connection) RETURN reply;
  FUNCTION open_connection(host  IN  VARCHAR2,
                           port  IN  PLS_INTEGER DEFAULT 25)
                           RETURN connection;                   

  /**
   * Sends a generic SMTP command and retrieves a single reply line.  If
   * multiple reply lines are returned from the SMTP server, the last reply
   * line is returned.
   *
   * PARAMETERS
   *   c     SMTP connection
   *   cmd   SMTP command
   *   arg   optional argument to the SMTP command
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error

invalid_operation - invalid operation */ FUNCTION command(c IN OUT NOCOPY connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL) RETURN reply; PROCEDURE command(c IN OUT NOCOPY connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL);

  /**
   * Sends a generic SMTP command and retrieves multiple reply lines.
   *
   * PARAMETERS
   *   c     SMTP connection
   *   cmd   SMTP command
   *   arg   optional argument to the SMTP command
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   invalid_operation - invalid operation
   */
  FUNCTION command_replies(c     IN OUT NOCOPY connection,
                           cmd   IN            VARCHAR2,
                           arg   IN            VARCHAR2 DEFAULT NULL)
                           RETURN replies;

  /**
   * Sends HELO command.
   *
   * PARAMETERS
   *   c        SMTP connection
   *   domain   domain of the sender
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error

invalid_operation - invalid operation */ FUNCTION helo(c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN reply; PROCEDURE helo(c IN OUT NOCOPY connection, domain IN VARCHAR2);

  /**
   * Sends EHLO command.
   *
   * PARAMETERS
   *   c        SMTP connection
   *   domain   domain of the sender
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */
  FUNCTION ehlo(c       IN OUT NOCOPY connection,
                domain  IN            VARCHAR2) RETURN replies;
  PROCEDURE ehlo(c       IN OUT NOCOPY connection,
                 domain  IN            VARCHAR2);

  /**
   * Sends MAIL command.
   *
   * PARAMETERS
   *   c           SMTP connection
   *   sender      the sender
   *   parameters  the optional parameters to MAIL command
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error

invalid_operation - invalid operation */ FUNCTION mail(c IN OUT NOCOPY connection, sender IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL) RETURN reply; PROCEDURE mail(c IN OUT NOCOPY connection, sender IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL);

  /**
   * Sends RCPT command.
   *
   * PARAMETERS
   *   c           SMTP connection
   *   recipient   the recipient
   *   parameters  the optional parameters to RCPT command
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */
  FUNCTION rcpt(c          IN OUT NOCOPY connection,
                recipient  IN            VARCHAR2,
                parameters IN            VARCHAR2 DEFAULT NULL) RETURN reply;
  PROCEDURE rcpt(c          IN OUT NOCOPY connection,
                 recipient  IN            VARCHAR2,
                 parameters IN            VARCHAR2 DEFAULT NULL);

  /**
   * Sends DATA command.  The data will be closed by the sequence
   * <CR><LF>.<CR><LF>
   *
   * PARAMETERS
   *   c     SMTP connection
   *   body  the data body
   * RETURN
   *   the SMTP reply after the sequence <CR><LF>.<CR><LF> is sent
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error

invalid_operation - invalid operation */ FUNCTION data(c IN OUT NOCOPY connection, body IN VARCHAR2) RETURN reply; PROCEDURE data(c IN OUT NOCOPY connection, body IN VARCHAR2);

  /**
   * Sends DATA command.  This call opens the data session that the
   * caller make subsequent write_data() calls to write large piece of
   * data, following by close_data() to close the data session.
   *
   * PARAMETERS
   *   c   SMTP connection
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */

FUNCTION open_data(c IN OUT NOCOPY connection) RETURN reply; PROCEDURE open_data(c IN OUT NOCOPY connection);

  /**
   * Sends data.  This call must be preceeded by the call open_data().
   *
   * PARAMETERS
   *   c      SMTP connection
   *   data   the data body
   * RETURN
   *   None
   * EXCEPTIONS
   *   invalid_operation - invalid operation
   */
  PROCEDURE write_data(c     IN OUT NOCOPY connection,
                       data  IN            VARCHAR2);

  /**
   * Sends DATA command.  This call opens the data session that the
   * caller make subsequent write_data() calls to write large piece of
   * data, following by close_data() to close the data session.
   *
   * PARAMETERS
   *   c   SMTP connection
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error

invalid_operation - invalid operation */ FUNCTION close_data(c IN OUT NOCOPY connection) RETURN reply; PROCEDURE close_data(c IN OUT NOCOPY connection);

  /**
   * Sends RSET command.
   *
   * PARAMETERS
   *   c   SMTP connection
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */

FUNCTION rset(c IN OUT NOCOPY connection) RETURN reply; PROCEDURE rset(c IN OUT NOCOPY connection);

  /**
   * Sends VRFY command.
   *
   * PARAMETERS
   *   c           SMTP connection
   *   recipient   the reccipient to verify
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   invalid_operation - invalid operation
   */
  FUNCTION vrfy(c          IN OUT NOCOPY connection,
                recipient  IN            VARCHAR2) RETURN reply;

  /**
   * Sends HELP command.
   *
   * PARAMETERS
   *   c         SMTP connection
   *   command   the command to get help message
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS

invalid_operation - invalid operation */ FUNCTION help(c IN OUT NOCOPY connection, command IN VARCHAR2 DEFAULT NULL) RETURN replies;

  /**
   * Sends NOOP command.
   *
   * PARAMETERS
   *   c   SMTP connection
   * RETURN
   *   the SMTP reply
   * EXCEPTIONS
   *   transient_error   - transient server error
   *   permanent_error   - permanent server error
   *   invalid_operation - invalid operation
   */

FUNCTION noop(c IN OUT NOCOPY connection) RETURN reply; PROCEDURE noop(c IN OUT NOCOPY connection);

  /**
   * Sends QUIT command.
   *
   * PARAMETERS
   *   c   SMTP connection
   * RETURN
   *   the SMTP reply

Received on Tue Oct 17 2000 - 00:02:12 CDT