Oracle UTL_TCP
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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-24247 network_access_denied_errcode: Network access denied
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
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 rekad 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 (new 12.2 parameter)
Secures a TCP/IP connection using SSL/TLS. Requires an Oracle Wallet spec. utl_tcp.secure_connection(
c           IN OUT NOCOPY connection
secure_host IN            VARCHAR2 DEFAULT NULL);
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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved