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   10/07/99 - Fix bug 708690: TAB -> blank
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;
