Re: How to send email from pl/sql in 806?

From: <OKhalid_at_lmkr.com>
Date: Fri, 25 May 2001 05:31:42 -0700
Message-ID: <F001.0030ED59.20010525052539@fatcity.com>

hi saurabh

nops utl_http will not help it's a different procedure here is the code in utltcp.sql file
hope it solves u'r problem

Omar Khalid
Software Engineer
LMKResources
A LANDMARK AFFILIATE
(Formerly Mathtech Pakistan Pvt. Ltd)
18, F-8/3, Main Margalla Road,
Islamabad, Pakistan
Voice: 111-101-101*780
Fax: 92-051-2255989
Email: okhalid_at_lmkr.com
Web: www.lmkr.com

REM Copyright (c) 1999 by Oracle Corporation. REM
REM NAME
REM utltcp.sql - PL/SQL Package for TCP/IP communication

REM                 (Package Specification of UTL_TCP)

REM
REM DESCRIPTION
REM Routines to perform TCP/IP 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/11/99 - Added constants for error codes
REM  rpang     07/28/99 - Added BAD_ARGUMENT exception
REM  rpang     05/14/99 - Created

CREATE OR REPLACE PACKAGE utl_tcp AS

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

OVERVIEW
*
This package provides TCP/IP client-side access functionality in PL/SQL.
With this package, a PL/SQL program can communicate with external TCP/IP
services and retrieve data. The API only allows connections to be
initiated by the PL/SQL program. It does not allow the PL/SQL program
to accept connections initiated from outside of the program.
*
USES
*
A TCP/IP connection is initiated by a call to open_connection, which
returns a TCP/IP connection. Text and binary data can be sent or
received on the connection. It is also possible to look ahead at the
data before it is actually read. The connection is terminated by
a call to close_connection.
*
EXAMPLES
Retrieve the home page from http://www.acme.com/
*
DECLARE
c utl_tcp.connection; -- TCP/IP connection to the Web server
BEGIN
c := utl_tcp.open_connection('www.acme.com', 80); -- open connection
*
utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send HTTP request
utl_tcp.write_line(c);
BEGIN
LOOP
dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result
END LOOP;
EXCEPTION
WHEN utl_tcp.end_of_input THEN
NULL; -- end of input
END;
utl_tcp.close_connection(c);
END;
*/

  /*
   * TCP connection type
   */

TYPE connection IS RECORD (

    remote_host   VARCHAR2(255),   -- Remote host name
    remote_port   PLS_INTEGER,     -- Remote port number
    local_host    VARCHAR2(255),   -- Local host name
    local_port    PLS_INTEGER,     -- Local port number
    charset       VARCHAR2(30),    -- Character set for on-the-wire comm.
    newline       VARCHAR2(2),     -- Newline character sequence
    private_sd    PLS_INTEGER,     -- For internal use only
    private_bf    RAW(32767),      -- For internal use only
    private_bfsz  PLS_INTEGER,     -- For internal use only
    private_pos   PLS_INTEGER,     -- For internal use only
    private_end   PLS_INTEGER,     -- For internal use only
    private_mkpos PLS_INTEGER      -- For internal use only

);

  /*
   * Carriage-return line-feed character sequence.
   */

CRLF CONSTANT VARCHAR2(10) := chr(13) || chr(10);

  /*
   * Exceptions
   */
  buffer_too_small  EXCEPTION;  -- Buffer is too small for I/O
  end_of_input      EXCEPTION;  -- End of input from the connection
  network_error     EXCEPTION;  -- Network error
  bad_argument      EXCEPTION;  -- Bad argument passed in API call
  buffer_too_small_errcode CONSTANT PLS_INTEGER:= -20000;
  end_of_input_errcode     CONSTANT PLS_INTEGER:= -20001;
  network_error_errcode    CONSTANT PLS_INTEGER:= -20002;
  bad_argument_errcode     CONSTANT PLS_INTEGER:= -20003;
  PRAGMA EXCEPTION_INIT(buffer_too_small, -20000);
  PRAGMA EXCEPTION_INIT(end_of_input,     -20001);
  PRAGMA EXCEPTION_INIT(network_error,    -20002);
  PRAGMA EXCEPTION_INIT(bad_argument,     -20003);

  /**

Opens a connection to a TCP/IP service. When connection is made
succesfully, the remote host name and remote port number will be stored in
the connection. If local_host, local_port or charset is specified,
it will be stored in the connection as well. *
PARAMETERS
remote_host remote host name to connect to
remote_port remote port number to connect to
local_host local host name to connect from
local_port local port number to connect from
in_buffer_size input buffer size
out_buffer_size output buffer size
charset character set for on-the-wire communication
newline newline character sequence
RETURN
a connection to the destinated TCP/IP service.
EXCEPTIONS
network_error - network error */ FUNCTION open_connection(remote_host IN VARCHAR2, remote_port IN PLS_INTEGER, local_host IN VARCHAR2 DEFAULT NULL, local_port IN PLS_INTEGER DEFAULT NULL, in_buffer_size IN PLS_INTEGER DEFAULT NULL, out_buffer_size IN PLS_INTEGER DEFAULT NULL, charset IN VARCHAR2 DEFAULT NULL, newline IN VARCHAR2 DEFAULT CRLF) RETURN connection;

  /**
   * Determines the number of bytes available for reading from a TCP/IP
   * connection.  It is the number of bytes that can be read immediately
   * without blocking.
   *
   * PARAMETERS
   *   c  TCP/IP connection
   * RETURN
   *   The number of bytes available for reading without blocking.
   * EXCEPTIONS
   *   network_error  - network error
   */

FUNCTION available(c IN OUT NOCOPY connection) RETURN PLS_INTEGER;

  /*----------------------- Binary Input/Output API
-----------------------*/

  /**
   * Reads binary data from a TCP/IP connection.  This function does not
   * return until the specified number of bytes have been read, or the end
   * of input has been reached.
   *
   * PARAMETERS
   *   c      TCP/IP connection
   *   data   the data read (OUT)
   *   len    the max number of bytes to read
   *   peek   should this call be peek-only (i.e. keep the data read
   *          in the input buffer to be read again later)?
   * RETURN
   *   The number of bytes read.  The actual number of bytes read may be
   * less than specified because the end of input has been reached.
   * EXCEPTIONS
   *   end_of_input   - end of input from the connection

network_error - network error */ FUNCTION read_raw(c IN OUT NOCOPY connection, data IN OUT NOCOPY RAW, len IN PLS_INTEGER DEFAULT 1, peek IN BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER;

  /**
   * Writes binary data to a TCP/IP connection.  This function does not
   * return until the specified number of bytes have been written.
   *
   * PARAMETERS
   *   c      TCP/IP connection
   *   data   the data to be written
   *   len    the number of bytes to write.  When len is NULL, the
   *          whole length of data is written.  The actual amount of
   *          data written may be less because of network condition
   * RETURN
   *   The actual number of bytes written to the connection.
   * EXCEPTIONS
   *   network_error  - network error
   */
  FUNCTION write_raw(c    IN OUT NOCOPY connection,
                     data IN            RAW,
                     len  IN            PLS_INTEGER DEFAULT NULL)
                     RETURN PLS_INTEGER;

  /*------------------------- Text Input/Output API
----------------------*/

  /**

Reads text data from a TCP/IP connection. This function does not
return until the specified number of characters have been read, or the end
of input has been reached. *
PARAMETERS
c TCP/IP connection
data the data read (OUT)
len the max number of characters to read
peek should this call be peek-only (i.e. keep the data read
in the input buffer to be read again later)?
RETURN
The number of characters read. The actual umber of characters read
may be less than specified because the end of input has been reached.
EXCEPTIONS
end_of_input - end of input from the connection
network_error - network error
NOTES
Text messages will be converted from the on-the-wire character set,
specified when the connection was opened, to the database character set
before they are returned to the caller. */ FUNCTION read_text(c IN OUT NOCOPY connection, data IN OUT NOCOPY VARCHAR2, len IN PLS_INTEGER DEFAULT 1, peek IN BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER;

  /**
   * Writes text data to a TCP/IP connection.  This function does not
   * return until the specified number of characters have been written.
   *
   * PARAMETERS
   *   c      TCP/IP connection
   *   data   the data to be written
   *   len    the number of characters to write.   When len is NULL,
   *          the whole length of data is written.  The amount of
   *          data returned may be less because of network condition
   * RETURN
   *   Then number of bytes written to the connection.
   * EXCEPTIONS
   *   network_error  - network error
   * NOTES
   *   Text messages will be converted to the on-the-wire character set,
   * specified when the connection was opened, before they are transmitted
   * on the wire.
   */
  FUNCTION write_text(c    IN OUT NOCOPY connection,
                      data IN            VARCHAR2,
                      len  IN            PLS_INTEGER DEFAULT NULL)
                      RETURN PLS_INTEGER;

  /*------------------- Line-oriented Input/Output API
----------------------*/

  /**
   * Reads a text line from a TCP/IP connection.  A line is terminated by
   * a line-feed, a carriage-return or a carriage-return followed by a
   * line-feed.  The function does not return until the end of line or the
   * end of input is reached.
   *
   * PARAMETERS
   *   c           TCP/IP connection
   *   data        the data read (OUT)
   *   remove_crlf remove the trailing new-line character(s) or not
   *   peek        should this call be peek-only (i.e. keep the data read
   *               in the input buffer to be read again later)?
   * RETURN
   *   The number of characters read.
   * EXCEPTIONS
   *   end_of_input   - end of input from the connection

network_error - network error */ FUNCTION read_line(c IN OUT NOCOPY connection, data IN OUT NOCOPY VARCHAR2, remove_crlf IN BOOLEAN DEFAULT FALSE, peek IN BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER;

  /**
   * Writes a text line to a TCP/IP connection.  The line is terminated
   * with the new-line character sequence sepecified when this connection
   * is opened.
   *
   * PARAMETERS
   *   c     TCP/IP connection
   *   data  the data to be written
   * RETURN
   *   Then number of bytes written to the connection.
   * EXCEPTIONS
   *   network_error  - network error
   */
  FUNCTION write_line(c    IN OUT NOCOPY connection,
                      data IN            VARCHAR2 DEFAULT NULL)
                      RETURN PLS_INTEGER;

  /*----------------- Convenient functions for Input API
------------------*/

  /**
   * A convenient form of the read functions, which return the data read
   * instead of the amount of data read.
   *
   * PARAMETERS
   *   c            TCP/IP connection
   *   len          the max number of bytes or characters to read
   *   removle_crlf remove the trailing new-line character(s) or not
   *   peek         should this call be peek-only (i.e. keep the data read
   *                in the input buffer to be read again later)?
   * RETURN
   *   The data (or line) read.
   * EXCEPTIONS
   *   end_of_input   - end of data from the connection

network_error - network error */ FUNCTION get_raw(c IN OUT NOCOPY connection, len IN PLS_INTEGER DEFAULT 1, peek IN BOOLEAN DEFAULT FALSE) RETURN RAW; FUNCTION get_text(c IN OUT NOCOPY connection, len IN PLS_INTEGER DEFAULT 1, peek IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2; FUNCTION get_line(c IN OUT NOCOPY connection, remove_crlf IN BOOLEAN DEFAULT false, peek IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;

  /**
   * Transmits all the output data in the output queue to the connection
   * immediately.
   *
   * PARAMETERS
   *   c   TCP/IP connection
   * RETURN
   *   None.
   * EXCEPTIONS
   *   network_error  - network error
   */

PROCEDURE flush(c IN OUT NOCOPY connection);

  /**
   * Closes a TCP/IP connection.  After the connection is closed, all the
   * in the connection will be set to NULL.
   *
   * PARAMETERS
   *   c    TCP/IP connection
   * RETURN
   *   None.
   * EXCEPTIONS
   *   network_error  - network error
   */

PROCEDURE close_connection(c IN OUT NOCOPY connection);

  /**
   * Closes all open TCP/IP connections.
   *
   * PARAMETERS
   *   None
   * RETURN
   *   None
   * EXCEPTIONS
   *   None
   */

PROCEDURE close_all_connections;

END;
/

GRANT EXECUTE ON sys.utl_tcp TO PUBLIC;
DROP PUBLIC SYNONYM utl_tcp;
CREATE PUBLIC SYNONYM utl_tcp FOR sys.utl_tcp;

                                                                                       
        
                                                                                       
        
                                                                                       
        


                                                                                       
                   
                                                                                       
                   
                                                                                       
                   


                                                                                       
                            
                    "Saurabh                                                           
                            
                    Sharma"              To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>       
                    <saurabhs_at_fcsl       cc:                                           
                            
                    td.com>              Subject:     Re: How to send email from 
pl/sql in 806?                    
                    Sent by:                                                           
                            
                    root_at_fatcity.c                                                     
                            
                    om                                                                 
                            
                                                                                       
                            
                                                                                       
                            
                    05/25/2001                                                         
                            
                    08:20 PM                                                           
                            
                    Please respond                                                     
                            
                    to ORACLE-L

hi,
i've oracle 8.1.5 on NT. but could'nt find the utltcp.sql , infact it had the procedure utl_http
could it help?

Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, May 25, 2001 1:56 PM

> hi sharma
> The builtin packages UTL_SMTP and UTL_TCP are available with oracle 8.1.5
> onwards , thats the reason you were getting compilation errors.
> Ramana
>
>
> > ----------
> > From: Saurabh Sharma[SMTP:saurabhs_at_fcsltd.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Friday, May 25, 2001 12:40 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: How to send email from pl/sql in 806?
> >
> > HI,
> >  i tried creating this procedure. but it results into compilation

errors.

> > says..
> > utl_tcp.connection must be declared, and so does for
> > utl_tcp.get_line and
> > utl_tcp.write_line
> >
> > how do we fefine them. where is this package utl_tcp
> >
> > pls explore.
> >
> > thanks.
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, May 23, 2001 11:05 AM
> >
> >
> > >
> > > try this procedure it works  just replace the IP address in the line
> > > c := utl_tcp.open_connection('192.168.1.1', 25);
> > > with the IP address of u'r mail server
> > >
> > > regards
> > > ______________________________________
> > > Omar Khalid
> > > Software Engineer
> > > LMKResources
> > > A LANDMARK AFFILIATE
> > > (Formerly Mathtech Pakistan Pvt. Ltd)
> > > 18, F-8/3, Main Margalla Road,
> > > Islamabad, Pakistan
> > > Voice: 111-101-101*780
> > > Fax: 92-051-2255989
> > > Email: okhalid_at_lmkr.com
> > > Web: www.lmkr.com
> > >
> > >
> > >
> > >
> > rem
> > -----------------------------------------------------------------------
> > > rem Filename:   smtp.sql
> > > rem Purpose:    Send e-mail messages from PL/SQL
> > > rem Notes:      From Oracle8i release 8.1.6 one can send e-mail
messages
> > > rem       directly from PL/SQL using either the UTL_TCP or UTL_SMTP
> > > rem       packages. No pipes or external procedures required.
> > > rem Date:       27-Mar-2000
> > > rem Author:     Frank Naude (frank_at_ibi.co.za)
> > >
> > rem
> > -----------------------------------------------------------------------
> > >
> > > CREATE OR REPLACE PROCEDURE SEND_MAIL (
> > >   msg_from    varchar2 := 'oracle',
> > >   msg_to      varchar2,
> > >   msg_subject varchar2 := 'E-Mail message from your database',
> > >   msg_text    varchar2 := '' )
> > > IS
> > >   c  utl_tcp.connection;
> > >   rc integer;
> > > BEGIN
> > >   c := utl_tcp.open_connection('192.168.1.1', 25);       -- open the
> > SMTP
> > > port 25 on local machine
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'HELO 192.168.1.1');
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'DATA');                 -- Start

message

> > > body
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
> > >   rc := utl_tcp.write_line(c, '');
> > >   rc := utl_tcp.write_line(c, msg_text);
> > >   rc := utl_tcp.write_line(c, '.');                    -- End of

message

> > > body
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   rc := utl_tcp.write_line(c, 'QUIT');
> > >   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
> > >   utl_tcp.close_connection(c);                         -- Close the
> > > connection
> > > EXCEPTION
> > >   when others then
> > >        raise_application_error(-20000,'Unable to send e-mail message
> > from
> > > pl/sql');
> > > END;
> > > /
> > > show errors
> > >
> > > --  Examples:
> > > set serveroutput on
> > >
> > > exec send_mail(msg_to  =>'Omar Khalid/IT/LotusCert/Pk');
> > > exec send_mail(msg_to  =>'omar_khalid_at_mathtechonline.com');
> > >
> > > exec send_mail(msg_to  =>'omar_khalid_at_mathtechonline.com',
> > >             msg_text=>'Look Ma I can send mail from plsql');
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> > >                     Oracle DBA
> > >                     <acur8dba_at_yaho       To:     Multiple recipients

> > list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >                     o.com>               cc:
> > >                     Sent by:             Subject:     How to send

> > from pl/sql in 806?
> > >                     root_at_fatcity.c
> > >                     om
> > >
> > >
> > >                     05/23/2001
> > >                     11:35 AM
> > >                     Please respond
> > >                     to ORACLE-L
> > >
> > >
> > >
> > >
> > >
> > > Hi,
> > >
> > > I am aware that 817 supports UTL_SMTP for this same
> > > functionality.  But how can one send email from pl/sql
> > > in 806?
> > >
> > >
> > > thanx
> > >
> > > =====
> > > Vicky D. Foster,
> > > Senior Oracle DBA:
> > > email:  acur8dba_at_yahoo.com
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! Auctions - buy the things you want at great prices
> > > http://auctions.yahoo.com/
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Oracle DBA
> > >   INET: acur8dba_at_yahoo.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).
> > >
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author:
> > >   INET: OKhalid_at_lmkr.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).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Saurabh Sharma
> >   INET: saurabhs_at_fcsltd.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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Venkata Ramana Kanchinadam
>   INET: vkanchinadam_at_sierraopt.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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Saurabh Sharma
  INET: saurabhs_at_fcsltd.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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: OKhalid_at_lmkr.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).

Received on Fri May 25 2001 - 07:31:42 CDT