Oracle UTL_TCP
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This package supports application communications with external TCP/IP-based servers using TCP/IP. This package can be used when PL/SQL code news to use Internet protocols and/or e-mail.
Note A maximum of 16 connections, per session, is allowed. See metalink Note:280838.1: ORA-30678 after executing UTL_TCP.OPEN_CONNECTION for details.
AUTHID CURRENT_USER
Constants
Name Data Type Value
CRLF VARCHAR2(2 CHAR) unistr('\000D\000A')
Data Types 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
tx_timeout  PLS_INTEGER,    -- Transfer time-out value (in seconds)
private_sd  PLS_INTEGER)    -- For internal use only
/
Dependencies
DBMS_AQELM UTL_CALL_STACK UTL_SMTP
DBMS_ISCHED UTL_MAIL UTL_TCP_LIB
SCHEDULER$_JOB_EVENT_HANDLER    
Documented Yes
Exceptions
Error Code Reason
ORA-29258 buffer_too_small_errcode: Buffer is too small for I/O
ORA-29259 end_of_input_errcode: End of input from the connection
ORA-29260 network_error_errcode: Network error
ORA-29261 bad_argument_errcode: Bad argument passed in API call
ORA-29275 partial_multibyte_char_errcode: A partial multi-byte character found
ORA-29276 transfer_timeout: Transfer time-out occurred
ORA-24247 network_access_denied_errcode: Network access denied
First Available 8.1.7
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utltcp.sql
Subprograms
 
AVAILABLE
Determines the number of bytes available for reading from a TCP/IP connection utl_tcp.available(
c       IN OUT NOCOPY connection,
timeout IN     PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER;
See READ_TEXT Demo Below
 
CLOSE_ALL_CONNECTIONS
Closes all open TCP/IP connections utl_tcp.close_all_connections;
exec utl_tcp.close_all_connections;
 
CLOSE_CONNECTION
Closes a TCP/IP connection utl_tcp.close_connection(c IN OUT NOCOPY connection);
See OPEN_CONNECTION Demo Below
 
FLUSH
Immediately transmits all the output data in the output queue to the connection utl_tcp.flush(c IN OUT NOCOPY connection);
See OPEN_CONNECTION Demo Below
 
GET_LINE
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_line(
c           IN OUT NOCOPY connection,
remove_crlf IN     BOOLEAN DEFAULT FALSE,
peek        IN     BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_LINE_NCHAR
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_line_nchar(
c           IN OUT NOCOPY connection,
remove_crlf IN     BOOLEAN DEFAULT FALSE,
peek        IN     BOOLEAN DEFAULT FALSE)
RETURN NVARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_RAW
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_raw(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN RAW;
See OPEN_CONNECTION Demo Below
 
GET_TEXT
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_text(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_TEXT_NCHAR
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_text_nchar(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN NVARCHAR2;
See OPEN_CONNECTION Demo Below
 
OPEN_CONNECTION
Opens a connection to a TCP/IP service utl_tcp.open_connection(
remote_host     VARCHAR2,
remote_port     PLS_INTEGER,
local_host      VARCHAR2    DEFAULT NULL,
local_port      PLS_INTEGER DEFAULT NULL,
in_buffer_size  PLS_INTEGER DEFAULT NULL,
out_buffer_size PLS_INTEGER DEFAULT NULL,
charset         VARCHAR2    DEFAULT NULL,
newline         VARCHAR2    DEFAULT CRLF,
tx_timeout      PLS_INTEGER DEFAULT NULL,
wallet_path     VARCHAR2    DEFAULT NULL,
wallet_password VARCHAR2    DEFAULT NULL)
RETURN connection;
set serveroutput on

spool c:\temp\utl_tcp.txt

DECLARE
 c      utl_tcp.connection; -- TCP/IP connection to the web server
 retval PLS_INTEGER;
BEGIN
  c := utl_tcp.open_connection(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
  utl_tcp.secure_connection(c);

  retval := utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send request
  retval := 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
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    utl_tcp.flush(c);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  utl_tcp.close_connection(c);
END;
/

spool off
A function that ping's ports to determine whether they exist CREATE OR REPLACE FUNCTION ping (pHostName VARCHAR2, pPort NUMBER DEFAULT 1000)
RETURN VARCHAR2 AUTHID DEFINER IS
 tcpCnx utl_tcp.connection;
 cOk    CONSTANT VARCHAR2(2) := 'OK';
 cFail  CONSTANT VARCHAR2(5) := 'ERROR';
BEGIN
  tcpCnx := utl_tcp.open_connection (pHostName, pPort);
  utl_tcp.close_connection(tcpCnx);
  RETURN cOk;
EXCEPTION
  WHEN utl_tcp.network_error THEN
    IF (UPPER(SQLERRM) LIKE '%HOST%') THEN
      RETURN cFail;
    ELSIF (UPPER(SQLERRM) LIKE '%LISTENER%') THEN
      RETURN cOk;
    ELSE
      RAISE;
    END IF;
WHEN OTHERS THEN
  RAISE;
END ping;
/

SELECT ping('usps997lt.usa.morganslibrary.com', 1521)
FROM dual;
 
READ_LINE
Reads a text line from a TCP/IP connection utl_tcp.read_line(
c           IN OUT NOCOPY connection,
data        IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
remove_crlf IN            BOOLEAN DEFAULT FALSE,
peek        IN            BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;
TBD
 
READ_RAW
Reads binary data from a TCP/IP connection utl_tcp.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;
TBD
 
READ_TEXT
Reads text data from a TCP/IP connection utl_tcp.read_text(
c    IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
len  IN            PLS_INTEGER DEFAULT 1,
peek IN            BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER;
DECLARE
 c    utl_tcp.connection;
 data VARCHAR2(256);
 len  PLS_INTEGER;
BEGIN
  c := utl_tcp.open_connection(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
  LOOP
    IF (utl_tcp.available(c) > 0) THEN
      len := utl_tcp.read_text(c, data, 256);
    ELSE
      ---do some other things
      NULL;
    END IF
  END LOOP;
  utl_tcp.close_connection(c);
END;
/
 
SECURE_CONNECTION
Secures a TCP/IP connection using SSL/TLS. Requires an Oracle Wallet spec. utl_tcp.secure_connection(c IN OUT NOCOPY connection);
See OPEN_CONNECTION Demo Above
 
WRITE_LINE
Writes a text line to a TCP/IP connection utl_tcp.write_line(
c    IN OUT NOCOPY connection,
data IN     VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL)
RETURN PLS_INTEGER;
See OPEN_CONNECTION Demo Above
 
WRITE_RAW
Writes binary data to a TCP/IP connection utl_tcp.write_raw(
c    IN OUT NOCOPY connection,
data IN            RAW,
len  IN            PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
TBD
 
WRITE_TEXT
Writes text data to a TCP/IP connection utl_tcp.write_text(
c    IN OUT NOCOPY connection,
data IN     VARCHAR2 CHARACTER SET ANY_CS,
len  IN     PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
TBD
 
Demos
Email Demo CREATE OR REPLACE PROCEDURE send_mail(
sender    IN VARCHAR2,
recipient IN VARCHAR2,
message   IN VARCHAR2)
AUTHID DEFINER IS
 mailhost   VARCHAR2(30) := 'smtp.drizzle.com';
 smtp_error EXCEPTION;
 mail_conn  utl_tcp.connection;

 -- embedded procedure
 PROCEDURE smtp_command(command IN VARCHAR2, ok IN VARCHAR2 DEFAULT '250') IS
  response VARCHAR2(256);
  len      PLS_INTEGER;
 BEGIN
   len := utl_tcp.write_line(mail_conn, command);
   response := utl_tcp.get_line(mail_conn);
   dbms_output.put_line(response);
   response := SUBSTR(response,1,3);
   IF (response <> ok) THEN
     RAISE smtp_error;
   END IF;
 END smtp_command;
 -- end embedded procedure
BEGIN
  mail_conn := utl_tcp.open_connection(remote_host => mailhost,
  remote_port => 25, charset => 'US7ASCII');
  smtp_command('HELO ' || mailhost);
  smtp_command('MAIL FROM: ' || sender);
  smtp_command('RCPT TO: ' || recipient);
  smtp_command('DATA', '354');
  smtp_command(message);
  smtp_command('QUIT', '221');
  utl_tcp.close_connection(mail_conn);
END send_mail;
/

exec send_mail('dan@morganslibrary.org', 'dan@morganslibrary.org', 'Test');

Related Topics
Packages
UTL_MAIL
UTL_SMTP

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved