TCP/IP package (UTL_TCP)

connection

Purpose

A PL/SQL record type to represent a TCP/IP connection.

Syntax

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 communication
    newline        VARCHAR2(2),   -- newline character sequence
    private_sd     PLS_INTEGER,   -- private, for implementation use
    private_bf     RAW(32767),    -- private, for implementation use
    private_bfsz   PLS_INTEGER,   -- private, for implementation use
    private_pos    PLS_INTEGER,   -- private, for implementation use
    private_end    PLS_INTEGER,   -- private, for implementation use
    private_mkpos  PLS_INTEGER    -- private, for implementation use
);

Parameters

remote_host

The name of the remote host when connection is established. NULL when no connection is established.

remote_port

The port number of the remote host connected. NULL when no connection is established.

local_host

The name of the local host used to establish the connection. NULL when no connection is established.

local_port

The port number of the local host used to establish the connection. NULL when no connection is established.

charset

The on-the-wire character set. Since text messages in the database may be encoded in a character set that is different from the one expected on the wire (i.e. the character set specified by the communication protocol, or the one stipulated by the other end of the communication), text messages in the database will be converted to and from the on-the-wire character set as they are sent and received on the network.

newline

The newline character sequence. This newline character sequence is appended to the text line sent by write_line() API.

private_sd, private_bf, private_bfsz, private_pos, private_end, private_mkpos

Private, for implementation use. Users should not modify these fields.

Comments

The fields "private_XXXX" are for implementation use only.

CRLF

Purpose

The character sequence carriage-return line-feed. It is the newline sequence commonly used many communication standards.

Syntax

CRLF constant varchar2(10) := chr(13) || chr(10);

Comments

This package constant defines the newline character sequence commonly used in many Internet protocols. This is the default value of the newline character sequence for write_line(), specified when a connection is opened. While such protocols use <CR><LF> to denote a new line, some implementations may choose to use just line-feed to denote a new line. In such cases, users can specify a different newline character sequence when a connection is opened.

open_connection()

Purpose

Open a TCP/IP connection to a specified service.

Syntax

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;

Parameters

remote_host (IN)

The name of the host providing the service. When remote_host is NULL, it connects to the local host.

remote_port (IN)

The port number on which the service is listening for connections.

local_host (IN)

The name of the host providing the service. NULL means don't care.

local_port (IN)

The port number on which the service is listening for connections. NULL means don't care.

in_buffer_size (IN)

The size of input buffer. The use of an input buffer can speed up execution performance in receiving data from the server. The appropriate size of buffer depends on the flow of data between the client and the server, and the network condition. A NULL or a 0 value means no buffer should be used. The maximum size of the input buffer is 32767 bytes.

out_buffer_size (IN)

The size of output buffer. The use of an output buffer can speed up execution performance in sending data to the server. The appropriate size of buffer depends on the flow of data between the client and the server, and the network condition. A NULL or a 0 value means no buffer should be used. The maximum size of the output buffer is 32767 bytes.

charset (IN)

The on-the-wire character set. Since text messages in the database may be encoded in a character set that is different from the one expected on the wire (i.e. the character set specified by the communication protocol, or the one stipulated by the other end of the communication), text messages in the database will be converted to and from the on-the-wire character set as they are sent and received on the network using read_text(), read_line(), write_text() and write_line(). Set this parameter to NULL when no conversion is needed.

newline (IN)

The newline character sequence. This newline character sequence is appended to the text line sent by write_line() API.

Comments

Note that connections opened by this UTL_TCP package can remain open and be passed from one database call to another in MTS configuration. However, the connection must be closed explicitly. The connection will remain open when the PL/SQL record variable that stores the connection goes out-of-scope in the PL/SQL program. Failing to close unwanted connections may result in unnecessary tying up of local and remote system resources.

Related Functions

close_connection(), close_all_connections()

available()

Purpose

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. Determines if data is ready to be read from the connection.

Syntax

FUNCTION available IN OUT NOCOPY connection) RETURN PLS_INTEGER;

Parameter

c (IN OUT NOCOPY)

The TCP connection to determine the amount of data that is available to be read from.

Comments

The connection must have already been opened via a call to open_connection(). Users may use this API to determine if data is available to be read before calling the read API so that the program will not be blocked because data is not ready to be read from the input.

Related Functions

read_raw(), read_text(), read_line()

read_raw()

Purpose

Receives binary data from a service on an open connection.

Syntax

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;

Parameters

c (IN OUT NOCOPY)

The TCP connection to receive data from.

data (IN OUT COPY)

The data received.

len (IN)

The number of bytes of data to receive.

peek (IN)

Normally, users want to read the data and remove it from the input queue, i.e. consuming it. In some situations, users may just want to look ahead at the data, i.e. peeking it, without removing it from the input queue so that it is still available for reading (or even peeking) in the next call. To keep the data in the input queue, set this flag to TRUE and an input buffer must be set up when the connection is opened. The amount of data that can be peeked (i.e. read but kept in the input queue) must be less the size of input buffer.

return value

The actual number of bytes of data received.

Comments

The connection must have already been opened via a call to open_connection(). This function does not return until the specified number of characters have been read, or the end of input has been reached. 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.

Related Functions

read_text(), read_line(), available()

write_raw()

Purpose

Transmits a binary message to a service on an open connection.

Syntax

FUNCTION write_raw(c    IN OUT NOCOPY connection,
                   data IN            RAW,
                   len  IN            PLS_INTEGER DEFAULT NULL) 
                                      RETURN PLS_INTEGER;

c (IN OUT NOCOPY)

The TCP connection to send data to.

data (IN)

The buffer containing the data to be sent.

len (IN)

The number of bytes of data to transmit. 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 value

The actual number of bytes of data transmitted.

Comments

The connection must have already been opened via a call to open_connection().

Related Functions

write_text(), write_line(), flush()

read_text()

Purpose

Receives text data from a service on an open connection.

Syntax

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;

c (IN OUT NOCOPY)

The TCP connection to receive data from.

data (IN OUT NOCOPY)

The data received.

len (IN)

The number of bytes of data to receive.

peek (IN)

Normally, users want to read the data and remove it from the input queue, i.e. consuming it. In some situations, users may just want to look ahead at the data, i.e. peeking it, without removing it from the input queue so that it is still available for reading (or even peeking) in the next call. To keep the data in the input queue, set this flag to TRUE and an input buffer must be set up when the connection is opened. The amount of data that can be peeked (i.e. read but kept in the input queue) must be less the size of input buffer.

return value

The actual number of characters of data received.

Comments

The connection must have already been opened via a call to open_connection(). This function does not return until the specified number of bytes have been read, or the end of input has been reached. 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.

Related Functions

read_raw(), read_line(), available()

write_text()

Purpose

Transmits a text message to a service on an open connection.

Syntax

FUNCTION write_text(c    IN OUT NOCOPY connection,
                    data IN            VARCHAR2,
                    len  IN            PLS_INTEGER DEFAULT NULL) 
                                       RETURN PLS_INTEGER;

c (IN OUT NOCOPY)

The TCP connection to send data to.

data (IN)

The buffer containing the data to be sent.

len (IN)

The number of characters of data to transmit. 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 value

The actual number of characters of data transmitted.

Comments

The connection must have already been opened via a call to open_connection(). 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.

Related Functions

write_raw(), write_line(), flush()

read_line()

Purpose

Receives a text line from a service on an open connection. A line is terminated by a line-feed, a carriage-return or a carriage-return followed by a line-feed.

Syntax

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;

c (IN OUT NOCOPY)

The TCP connection to receive data from.

data (IN OUT NOCOPY)

The data received.

remove_crlf (IN)

If TRUE, the trailing CR/LF character(s) are removed from the received message.

peek (IN)

Normally, users want to read the data and remove it from the input queue, i.e. consuming it. In some situations, users may just want to look ahead at the data, i.e. peeking it, without removing it from the input queue so that it is still available for reading (or even peeking) in the next call. To keep the data in the input queue, set this flag to TRUE and an input buffer must be set up when the connection is opened. The amount of data that can be peeked (i.e. read but kept in the input queue) must be less the size of input buffer.

return value

The actual number of characters of data received.

Comments

The connection must have already been opened via a call to open_connection(). This function does not return until the end-of-line have been reached, or the end of input has been reached. 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.

Related Functions

read_raw(), read_text(), available()

write_line()

Purpose

Transmits a text line to a service on an open connection. The newline character sequence will be appended to the message before it is transmitted.

Syntax

FUNCTION write_line(c    IN OUT NOCOPY connection,
                    data IN            VARCHAR2 DEFAULT NULL) 
                                       RETURN PLS_INTEGER;

c (IN OUT NOCOPY)

The TCP connection to send data to.

data (IN)

The buffer containing the data to be sent.

return value

The actual number of characters of data transmitted.

Comments

The connection must have already been opened via a call to open_connection(). 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.

Related Functions

write_raw(), write_text(), flush()

get_raw(), get_text(), get_line()

Purpose

Convenient forms of the read functions, which return the data read instead of the amount of data read.

Syntax

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;

c (IN OUT NOCOPY)

The TCP connection to receive data from.

len (IN)

The number of bytes (or characters for VARCHAR2) of data to receive. Default is 1.

peek (IN)

Normally, users want to read the data and remove it from the input queue, i.e. consuming it. In some situations, users may just want to look ahead at the data, i.e. peeking it, without removing it from the input queue so that it is still available for reading (or even peeking) in the next call. To keep the data in the input queue, set this flag to TRUE and an input buffer must be set up when the connection is opened. The amount of data that can be peeked (i.e. read but kept in the input queue) must be less the size of input buffer.

remove_crlf (IN)

If TRUE, the trailing CR/LF character(s) are removed from the received message.

Comments

The connection must have already been opened via a call to open_connection().

Related Functions

read_raw(), read_text(), read_line()

flush()

Purpose

Transmits all data in the output buffer, if a buffer is used, to the server immediately.

Syntax

PROCEDURE flush (c IN OUT NOCOPY connection);

Parameter

c (IN OUT NOCOPY)

The TCP connection to send data to.

Comments

The connection must have already been opened via a call to open_connection().

Related Functions

write_raw(), write_text(), write_line()

close_connection()

Purpose

Closes an open TCP/IP connection.

Syntax

PROCEDURE close_connection (c IN OUT NOCOPY connection);

Parameter

c (IN OUT NOCOPY)

The TCP connection to close.

Comments

Connection must have been opened by a previous call to open_connection(). The fields "remote_host", "remote_port", "local_host", "local_port" and "charset" of c will be reset after the connection is closed.

An open connection must be closed explicitly. An open connection will remain open when the PL/SQL record variable that stores the connection goes out-of-scope in the PL/SQL program. Failing to close unwanted connections may result in unnecessary tying up of local and remote system resources.

close_all_connections()

Purpose

Closes all open TCP/IP connections.

Syntax

PROCEDURE close_all_connections;

Parameters

None.

Comments

This call is provided to close all connections before a PL/SQL program avoid dangling connections.

Related Functions

open_connection(), close_connection()