With the UTL_TCP
package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail.
This chapter contains the following topics:
Overview
Types
Exceptions
Rules and Limits
Examples
The UTL_TCP
package provides TCP/IP client-side access functionality in PL/SQL.
This is a PL/SQL record type used to represent a TCP/IP connection.
TYPE connection IS RECORD ( remote_host VARCHAR2(255), remote_port PLS_INTEGER, local_host VARCHAR2(255), local_port PLS_INTEGER, charset VARCHAR2(30), newline VARCHAR2(2), tx_timeout PLS_INTEGER, private_sd PLS_INTEGER);
Table 179-1 Connection Record Type Fields
Field | Description |
---|---|
|
The name of the remote host when connection is established. |
|
The port number of the remote host connected. NULL when no connection is established. |
|
The name of the local host used to establish the connection. |
|
The port number of the local host used to establish the connection. |
|
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. |
|
The newline character sequence. This newline character sequence is appended to the text line sent by |
|
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. |
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.
The character sequence carriage-return line-feed. It is the newline sequence commonly used many communication standards.
CRLF varchar2(10);
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.
The exceptions raised by the TCP/IP package are listed in Table 179-2.
Exception | Description |
---|---|
|
Buffer is too small for input that requires look-ahead. |
|
Raised when no more data is available to read from the connection. |
|
Generic network error. |
|
Bad argument passed in an API call (for example, a negative buffer size). |
|
No data is read and a read time out occurred. |
|
No complete character is read and a partial multibyte character is found at the end of the input. |
The interface provided in the package only allows connections to be initiated by the PL/SQL program. It does not allow the PL/SQL program to accept connections initiated outside the program.
The following code example illustrates how the TCP/IP package can be used to retrieve a Web page over HTTP. It connects to a Web server listening at port 80 (standard port for HTTP) and requests the root document.
DECLARE c utl_tcp.connection; -- TCP/IP connection to the Web server ret_val pls_integer; BEGIN c := utl_tcp.open_connection(remote_host => 'www.acme.com', remote_port => 80, charset => 'US7ASCII'); -- open connection ret_val := utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send HTTP request ret_val := 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;
The following code example illustrates how the TCP/IP package can be used by an application to send e-mail (also known as email from PL/SQL). The application connects to an SMTP server at port 25 and sends a simple text message.
PROCEDURE send_mail (sender IN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2) IS mailhost VARCHAR2(30) := 'mailhost.mydomain.com'; smtp_error EXCEPTION; mail_conn utl_tcp.connection; PROCEDURE smtp_command(command IN VARCHAR2, ok IN VARCHAR2 DEFAULT '250') IS response varchar2(3); len pls_integer; BEGIN len := utl_tcp.write_line(mail_conn, command); response := substr(utl_tcp.get_line(mail_conn), 1, 3); IF (response <> ok) THEN RAISE smtp_error; END IF; END; 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); EXCEPTION WHEN OTHERS THEN -- Handle the error END;
Table 179-3 UTL_TCP Package Subprograms
Subprogram | Description |
---|---|
Determines the number of bytes available for reading from a TCP/IP connection |
|
Closes all open TCP/IP connections |
|
Closes an open TCP/IP connection |
|
Transmits all data in the output buffer, if a buffer is used, to the server immediately |
|
Convenient forms of the read functions, which return the data read instead of the amount of data read |
|
Convenient forms of the read functions, which return the data read instead of the amount of data read |
|
Convenient forms of the read functions, which return the data read instead of the amount of data read |
|
Opens a TCP/IP connection to a specified service |
|
Receives a text line from a service on an open connection |
|
Receives binary data from a service on an open connection |
|
Receives text data from a service on an open connection |
|
Transmits a text line to a service on an open connection |
|
Transmits a binary message to a service on an open connection |
|
Transmits a text message to a service on an open connection |
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.
UTL_TCP.AVAILABLE ( c IN OUT NOCOPY connection, timeout IN PLS_INTEGER DEFAULT 0) RETURN num_bytes PLS_INTEGER;
Table 179-4 AVAILABLE Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to determine the amount of data that is available to be read from. |
|
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. |
Table 179-5 AVAILABLE Function Return Values
Parameter | Description |
---|---|
|
The number of bytes available for reading without blocking. |
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. This function returns a positive value when all the data at a particular connection has been read and the next read will result in the END_OF_INPUT
exception.
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;
READ_RAW, READ_TEXT, READ_LINE
This procedure closes all open TCP/IP connections.
UTL_TCP.CLOSE_ALL_CONNECTIONS;
This call is provided to close all connections before a PL/SQL program avoid dangling connections.
OPEN_CONNECTION,
CLOSE_CONNECTION.
This procedure closes an open TCP/IP connection.
UTL_TCP.CLOSE_CONNECTION ( c IN OUT NOCOPY connection);
Table 179-6 CLOSE_CONNECTION Procedure Parameters
Parameter | Description |
---|---|
|
The TCP connection to close. |
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.
This procedure transmits all data in the output buffer, if a buffer is used, to the server immediately.
UTL_TCP.FLUSH ( c IN OUT NOCOPY connection);
The connection must have already been opened through a call to OPEN_CONNECTION
.
WRITE_RAW, WRITE_TEXT, WRITE_LINE
This function returns 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;
Table 179-8 GET_LINE Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
If |
|
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 |
The connection must have already been opened through a call to OPEN_CONNECTION
.
See READ_LINE
for the read time out, character set conversion, buffer size, and multibyte character issues.
GET_RAW
, GET_TEXT
, READ_LINE
This function returns 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;
Table 179-9 GET_RAW Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
The number of bytes (or characters for |
|
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 |
|
If |
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.
GET_RAW
, GET_TEXT
, READ_RAW, READ_TEXT, READ_LINE
This function returns 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;
Table 179-10 GET_TEXT Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
The number of bytes (or characters for |
|
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 |
|
If |
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.
READ_RAW, READ_TEXT, READ_LINE
This function opens a TCP/IP connection to a specified service.
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;
Table 179-11 OPEN_CONNECTION Function Parameters
Parameter | Description |
---|---|
|
The name of the host providing the service. When |
|
The port number on which the service is listening for connections. |
|
The name of the host providing the service. NULL means don't care. |
|
The port number on which the service is listening for connections. NULL means don't care. |
|
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. |
|
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. |
|
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 |
|
The newline character sequence. This newline character sequence is appended to the text line sent by |
|
A time in seconds that the |
Table 179-12 OPEN_CONNECTION Function Return Values
Parameter | Description |
---|---|
|
A connection to the targeted TCP/IP service. |
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.
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.
CLOSE_CONNECTION, CLOSE_ALL_CONNECTIONS
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.
UTL_TCP.READ_LINE ( c IN OUT NOCOPY connection, data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS, peek IN BOOLEAN DEFAULT FALSE) RETURN num_chars PLS_INTEGER;
Table 179-13 READ_LINE Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
The data received. |
|
If |
|
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 |
Table 179-14 READ_LINE Function Return Values
Parameter | Description |
---|---|
|
The actual number of characters of data received. |
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.
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.
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.
READ_RAW, READ_TEXT, AVAILABLE
This function receives binary data from a service on an open 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 num_bytes PLS_INTEGER;
Table 179-15 READ_RAW Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
The data received. |
|
The number of bytes of data to receive. |
|
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 |
Table 179-16 READ_RAW Function Return Values
Parameter | Description |
---|---|
|
The actual number of bytes of data received. |
The connection must have already been opened through 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.
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.
READ_TEXT, READ_LINE, AVAILABLE
This function receives text data from a service on an open 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 num_chars PLS_INTEGER;
Table 179-17 READ_TEXT Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to receive data from. |
|
The data received. |
|
The number of characters of data to receive. |
|
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 |
Table 179-18 READ_TEXT Function Return Values
Parameter | Description |
---|---|
|
The actual number of characters of data received. |
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 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.
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.
READ_RAW, READ_LINE, AVAILABLE
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.
UTL_TCP.WRITE_LINE ( c IN OUT NOCOPY connection, data IN VARCHAR2 DEFAULT NULL CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
Table 179-19 WRITE_LINE Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to send data to. |
|
The buffer containing the data to be sent. |
Table 179-20 WRITE_LINE Function Return Values
Parameter | Description |
---|---|
|
The actual number of characters of data transmitted. |
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.
WRITE_RAW, WRITE_TEXT, FLUSH
This function transmits a binary message to a service on an open connection. The function does not return until the specified number of bytes have been written.
UTL_TCP.WRITE_RAW ( c IN OUT NOCOPY connection, data IN RAW, len IN PLS_INTEGER DEFAULT NULL) RETURN num_bytes PLS_INTEGER;
Table 179-21 WRITE_RAW Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to send data to. |
|
The buffer containing the data to be sent. |
|
The number of bytes of data to transmit. When |
Table 179-22 WRITE_RAW Function Return Values
Parameter | Description |
---|---|
|
The actual number of bytes of data transmitted. |
The connection must have already been opened through a call to OPEN_CONNECTION.
WRITE_TEXT, WRITE_LINE, FLUSH
This function transmits a text message to a service on an open connection.
UTL_TCP.WRITE_TEXT ( c IN OUT NOCOPY connection, data IN VARCHAR2 CHARACTER SET ANY_CS, len IN PLS_INTEGER DEFAULT NULL) RETURN num_chars PLS_INTEGER;
Table 179-23 WRITE_TEXT Function Parameters
Parameter | Description |
---|---|
|
The TCP connection to send data to. |
|
The buffer containing the data to be sent. |
|
The number of characters of data to transmit. When |
Table 179-24 WRITE_TEXT Function Return Values
Parameter | Description |
---|---|
|
The actual number of characters of data transmitted. |
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.
WRITE_RAW,
WRITE_LINE, FLUSH