| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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:
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);
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 165-2.
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;
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;
| 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. 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);
| 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);
| Parameter | Description |
|---|---|
|
|
The TCP connection to send data to. |
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;
OPEN_CONNECTION.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;
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;
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;
| 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;
| 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;
| 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;
| 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;
| Parameter | Description |
|---|---|
|
|
The TCP connection to send data to. |
|
|
The buffer containing the data to be sent. |
| 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;
| 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;
| 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
|
 Copyright © 1998, 2003 Oracle Corporation All Rights Reserved. |
|