UTL_TCP , 2 of 2

Summary of UTL_TCP Subprograms

Table 101-2 UTL_TCP Subprograms

Subprogram	Description
connection	A PL/SQL record type used to represent a TCP/IP connection.
CRLF	The character sequence carriage-return line-feed. It is the newline sequence commonly used many communication standards.
open_connection Function	Opens a TCP/IP connection to a specified service.
available Function	Determines the number of bytes available for reading from a TCP/IP connection.
read_raw Function	Receives binary data from a service on an open connection.
write_raw Function	Transmits a binary message to a service on an open connection.
read_text Function	Receives text data from a service on an open connection.
write_text Function	Transmits a text message to a service on an open connection.
read_line Function	Receives a text line from a service on an open connection.
write_line Function	Transmits a text line to a service on an open connection.
get_raw(), get_text(), get_line() Functions	Convenient forms of the read functions, which return the data read instead of the amount of data read.
flush Procedure	Transmits all data in the output buffer, if a buffer is used, to the server immediately.
close_connection Procedure	Closes an open TCP/IP connection.
close_all_connections Procedure	Closes all open TCP/IP connections.

connection

This is a PL/SQL record type used 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
    tx_timeout     PLS_INTEGER,   -- transfer time-out value (in seconds)
    private_sd     PLS_INTEGER,   -- for internal use
    );

Fields

Table 101-3 connection Record Type Fields

Field	Description
`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 (that is, 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.
`tx_timeout`	A time in seconds that the UTL_TCP package waits before giving up in a read or write operation in this connection. In read operations, this package gives up if no data is available for reading immediately. In write operations, this package gives up if the output buffer is full and no data is to be sent in the network without being blocked. Zero (0) indicates not to wait at all. NULL indicates to wait forever.

Usage Notes

The fields in a connection record are used to return information about the connection, which is often made using open_connection(). Changing the values of those fields has no effect on the connection. The fields private_XXXX are for implementation use only. You should not modify the values.

In the current release of the UTL_TCP package, the parameters local_host and local_port are ignored when open_connection makes a TCP/IP connection. It does not attempt to use the specified local host and port number when the connection is made. The local_host and local_port fields will not be set in the connection record returned by the function.

Time-out on write operations is not supported in the current release of the UTL_TCP package.

CRLF

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

Syntax

CRLF varchar2(10);

Usage Notes

This package variable 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.

This CRLF package variable is intended to be a constant that denotes the carriage- return line-feed character sequence. Do not modify its value. Modification may result in errors in other PL/SQL applications.

open_connection Function

This function opens a TCP/IP connection to a specified service.

Syntax

UTL_TCP.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,
                          tx_timeout       IN PLS_INTEGER DEFAULT NULL)
                          RETURN connection;

Parameters

Table 101-4 open_connection Function Parameters

Parameter	Description
`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 the buffer depends on the flow of data between the client and the server, and the network condition. A 0 value means no buffer should be used. A NULL value means the caller does not care if a buffer is used or not. 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 0 value means no buffer should be used. A NULL value means the caller does not care if a buffer is used or not. 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 (that is, 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.
tx_timeout	A time in seconds that the `UTL_TCP` package should wait before giving up in a read or write operations in this connection. In read operations, this package gives up if no data is available for reading immediately. In write operations, this package gives up if the output buffer is full and no data is to be sent in the network without being blocked. Zero (0) indicates not to wait at all. NULL indicates to wait forever.

Usage Notes

Note that connections opened by this UTL_TCP package can remain open and be passed from one database call to another in a shared server 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.

The parameters local_host and local_port are ignored currently when open_connection makes a TCP/IP connection. It does not attempt to use the specified local host and port number when the connection is made.

Time-out on write operations is not supported in the current release of the UTL_TCP package.

Related Functions

close_connection(), close_all_connections()

available Function

This function 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

UTL_TCP.AVAILABLE (
   c        IN OUT NOCOPY connection, 
   timeout  IN PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER;

Parameters

Table 101-5 Available Function Parameters

Parameter	Description
c (IN OUT NOCOPY)	The TCP connection to determine the amount of data that is available to be read from.
timeout	A time in seconds to wait before giving up and reporting that no data is available. Zero (0) indicates not to wait at all. NULL indicates to wait forever.

Usage Notes

The connection must have already been opened through 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.

The number of bytes available for reading returned by this function may less than than what is actually available. On some platforms, this function may only return 1, to indicate that some data is available. If you are concerned about the portability of your application, assume that this function returns a positive value when data is available for reading, and 0 when no data is available. The following example illustrates using this function in a portable manner:

DECLARE
   c   utl_tcp.connection
   data VARCHAR2(256);
   len  PLS_INTEGER;
BEGIN
   c := utl_tcp.open_connection(...);
   LOOP
      IF (utl_tcp.available(c) > 0) THEN
         len := utl_tcp.read_text(c, data, 256);
      ELSE
         ---do some other things
        . . . .
      END IF
   END LOOP;
END;

Related Functions

read_raw(), read_text(), read_line()

read_raw Function

This function receives binary data from a service on an open connection.

Syntax

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;

Parameters

Table 101-6 read_raw Function Parameters

Parameter	Description
`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, you want to read the data and remove it from the input queue, that is, consume it. In some situations, you may just want to look ahead at the data, that is, peek at 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 set up an input buffer before the connection is opened. The amount of data you can peeked at (that is, read but keep in the input queue) must be less than the size of input buffer.
`return value`	The actual number of bytes of data received.

Usage Notes

The connection must have already been opened through 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.

If transfer time-out is set when the connection is opened, this function waits for each data packet to be ready to read until time-out occurs. If it occurs, this function stops reading and returns all the data read successfully. If no data is read successfully, the transfer_timeout exception is raised. The exception can be handled and the read operation can be retried later.

Related Functions

read_text(), read_line(), available()

write_raw Function

This function transmits a binary message to a service on an open connection.

Syntax

UTL_TCP.WRITE_RAW (c    IN OUT NOCOPY connection,
                   data IN            RAW,
                   len  IN            PLS_INTEGER DEFAULT NULL) 
                                      RETURN PLS_INTEGER;

Table 101-7 write_raw Function Parameters

Parameter	Description
`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.

Usage Notes

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

Related Functions

write_text(), write_line(), flush()

read_text Function

This function receives text data from a service on an open connection.

Syntax

UTL_TCP.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;

Table 101-8 read_text Function Parameters

Parameter	Description
c (IN OUT NOCOPY)	The TCP connection to receive data from.
`data (IN OUT NOCOPY)`	The data received.
`len (IN)`	The number of characters of data to receive.
`peek (IN)`	Normally, users want to read the data and remove it from the input queue, that is, consume it. In some situations, users may just want to look ahead at the data 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 you can peek at (that is, read but keep in the input queue) must be less than the size of input buffer.
`return value`	The actual number of characters of data received.

Usage Notes

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

Unless explicitly overridden, the size of a VARCHAR2 buffer is specified in terms of bytes, while the parameter len refers to the maximum number of characters to be read. When the database character set is multibyte, where a single character may consist of more than 1 byte, you should ensure that the buffer can hold the maximum of characters. In general, the size of the VARCHAR2 buffer should equal the number of characters to be read, multiplied by the maximum number of bytes of a character of the database character set.

If a partial multibyte character is found at the end of input, this function stops reading and returns all the complete multibyte characters read successfully. If no complete character is read successfully, the partial_multibyte_char exception is raised. The exception can be handled and the bytes of that partial multibyte character can be read as binary by the read_raw function. If a partial multibyte character is seen in the middle of the input because the remaining bytes of the character have not arrived and read time-out occurs, the transfer_timeout exception is raised instead. The exception can be handled and the read operation can be retried later.

Related Functions

read_raw(), read_line(), available()

write_text Function

This function transmits a text message to a service on an open connection.

Syntax

UTL_TCP.WRITE_TEXT (c    IN OUT NOCOPY connection,
                    data IN            VARCHAR2,
                    len  IN            PLS_INTEGER DEFAULT NULL) 
                                       RETURN PLS_INTEGER;

Table 101-9 write_text Function Parameters

Parameter	Description
`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.

Usage Notes

The connection must have already been opened through 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 Function

This function 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

UTL_TCP.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;

Table 101-10 read_line Function Parameters

Parameter	Description
`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, you want to read the data and remove it from the input queue, that is, consume it. In some situations, you may just want to look ahead at the data, that is, peek at 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 set up an input buffer before the connection is opened. The amount of data you can peeked at (that is, read but keep in the input queue) must be less than the size of input buffer.
`return value`	The actual number of characters of data received.

Usage Notes

The connection must have already been opened through 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 Function

This function 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

UTL_TCP.WRITE_LINE (c    IN OUT NOCOPY connection,
                    data IN            VARCHAR2 DEFAULT NULL) 
                                       RETURN PLS_INTEGER;

Table 101-11 write_line Function Parameters

Parameter	Description
`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.

Usage Notes

Related Functions

write_raw(), write_text(), flush()

get_raw(), get_text(), get_line() Functions

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

Syntax

UTL_TCP.GET_RAW (c     IN OUT NOCOPY connection,
                 len   IN            PLS_INTEGER DEFAULT 1,
                 peek  IN            BOOLEAN     DEFAULT FALSE) RETURN RAW;
UTL_TCP.GET_TEXT (c    IN OUT NOCOPY connection,
                  len  IN            PLS_INTEGER DEFAULT 1,
                  peek IN            BOOLEAN     DEFAULT FALSE) RETURN VARCHAR2;
UTL_TCP.GET_LINE (c           IN OUT NOCOPY connection,
                  remove_crlf IN            BOOLEAN DEFAULT false,
                  peek        IN            BOOLEAN DEFAULT FALSE) RETURN 
VARCHAR2;

Table 101-12 get_raw(), get_text(), and get_line() Function Parameters

Parameter	Description
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, you want to read the data and remove it from the input queue, that is, consume it. In some situations, you may just want to look ahead at the data, that is, peek at 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 set up an input buffer before the connection is opened. The amount of data you can peeked at (that is, read but keep in the input queue) must be less than the size of input buffer.
remove_crlf (IN)	If TRUE, the trailing CR/LF character(s) are removed from the received message.

Usage Notes

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

For all the get_* APIs described in this section, see the corresponding read_* API for the read time-out issue. For get_text and get_line, see the corresponding
read_* API for character set conversion, buffer size, and multibyte character issues.

Related Functions

read_raw(), read_text(), read_line()

flush Procedure

This procedure transmits all data in the output buffer, if a buffer is used, to the server immediately.

Syntax

UTL_TCP.FLUSH (c IN OUT NOCOPY connection);

Parameters

Table 101-13 flush Procedure Parameters

Parameter	Description
`c (IN OUT NOCOPY)`	The TCP connection to send data to.

Usage Notes

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

Related Functions

write_raw(), write_text(), write_line()

close_connection Procedure

This procedure closes an open TCP/IP connection.

Syntax

UTL_TCP.close_CLOSE_CONNECTION (c IN OUT NOCOPY connection);

Parameters

Table 101-14 close_connection Procedure Parameters

Parameter	Description
`c (IN OUT NOCOPY)`	The TCP connection to close.

Usage Notes

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 Procedure

This procedure closes all open TCP/IP connections.

Syntax

UTL_TCP.CLOSE_ALL_CONNECTIONS;

Usage Notes

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

Related Functions

open_connection(), close_connection()