Oracle UTL_TCP Tips

The UTL_TCP (Transfer Control Protocol) helps the PL/SQL programs to communicate with the external servers using the TCP/IP as it is the common communication protocol of the internet. This package provides the client side access functionality of the TCP/IP protocol through PL/SQL by opening up a socket to read/ write data from/ to a server using any protocol like HTTP, SMTP, or FTP.

The different objects available in this package are described below.

CONNECTION Record Type

This is a PL/SQL record type for establishing a TCP/IP connection through PL/SQL subprograms. The fields in the CONNECTION record type are used to provide salient information about the TCP/IP connection being established.

The prototype of the CONNECTION record type is shown below,

·         REMOTE_HOST parameter contains the name of the remote host during a connection. Null if no connection is established.

·         REMOTE_PORT parameter contains the port of the remote host during a connection. Null if no connection is established.

·         LOCAL_HOST parameter contains the name of the local host used for the connection. Null if no connection is established. This parameter is currently unsupported.

·         LOCAL_PORT parameter contains the port of the local host used for the connection. Null if no connection is established. This parameter is currently unsupported.

·         CHARSET parameter contains the on-the-wire character set for converting the character set of the database data to and from the connected internet site’s character set.

·         NEWLINE parameter contains the sequence of the newline character.

·         TX_TIMEOUT parameter contains the timeout time in seconds before giving up on a connection while trying.

·         PRIVATE_SD parameter is used for the package’s internal use and must not be manipulated.

OPEN_CONNECTION Function

This function opens up a TCP/IP protocol based connection to the specified web service. The connections opened up by this function remains open and can be transferred between the databases using the shared server configuration. These connections must be closed explicitly and failing in closing the unwanted connections may result in holding up of the system resources.

The prototype of the OPEN_CONNECTION function is shown below,

·         IN_BUFFER_SIZE parameter accepts the size of the input buffer for speeding up the execution performance of the receiving data from the server.

·         OUT_BUFFER_SIZE parameter accepts the size of the output buffer for speeding up the execution performance of the sending data to the server.

·         WALLET_PATH parameter accepts the wallet path for the certificates stored which are required while requesting data from a secure connection. The format is file: <Directory_path>.

·         WALLET_PASSWORD parameter accepts the password to open the wallet. When AUTO_LOGIN parameter is enabled, this parameter may set to null.

SECURE_CONNECTION Procedure

This procedure secures the TCP/IP connection made using SSL/TLS configuration. This process needs an Oracle wallet with a valid certificate to be specified during the connection call using the OPEN_CONNECTION function.

The prototype of the SECURE_CONNECTION procedure is shown below,

·         C parameter accepts and returns the connection details of the server from which the data is to be received.

UTL_TCP AVAILABLE Function

This function returns the number of bytes available for reading from the connected server without blocking after making a connection. On some platforms, this function may return 1 if there are data to receive from the server and 0 if not. This function is used to do a precautionary check whether the data is available or not before reading before allocating resources for the data read.

The prototype of the AVAILABLE function is shown below,

·         TIMEOUT parameter accepts the timeout time in seconds before giving up on a connection while trying.

FLUSH Procedure

This procedure flushes all the data into the server from the output buffer after establishing a connection.

The prototype of the FLUSH procedure is shown below,

GET_LINE, and GET_LINE_NCHAR Functions

The functions GET_LINE and GET_LINE_NCHAR, return line of data read in the database and national character set respectively.

The prototype of the GET_LINE and the GET_LINE_NCHAR functions are shown below,

·         REMOVE_CRLF parameter removes the trailing CRLF characters from the received message.

·         PEEK parameter is to be set to a Boolean true when we want to look ahead at the data without removing it from the queue so that it's available for reading in the further call. This parameter needs an input buffer to be created before the connection is opened, which then holds the data for peeking without interfering this data from the next call.

GET_RAW, GET_TEXT, and GET_TEXT_NCHAR Functions

The GET_RAW, GET_TEXT, and GET_TEXT_NCHAR functions return the RAW data, TEXT data, TEXT data in national character read from the target server.

The prototypes of these functions are shown below,

·         LEN parameter accepts the number of bytes of data to be received.

READ_LINE

This function receives a text line from the target server upon an open connection. A line feed, a carriage-return or a carriage return followed by a linefeed determines the line separator. This function returns the number of characters of data received through its RETURN statement.

The prototype of this function is shown below,

·         DATA parameter accepts and returns the data to be received.

READ_RAW, and READ_TEXT Functions

The READ_RAW and READ_TEXT functions receive binary, and text data respectively from the target server with an open connection.

These functions do not return data unless the specified number of bytes is read or the EOI (End of Input) has been reached. The size of the VARCHAR2 buffer should be equal to the number of characters to be read, multiplied by the maximum number of bytes of a character of the database character set. These functions return the number of characters of data received through its RETURN statement.

The prototypes of these functions are shown below,

WRITE_LINE, WRITE_RAW, and WRITE_TEXT Functions

The WRITE_LINE, WRITE_RAW, and WRITE_TEXT functions write a text line, a binary message, and a text message respectively to the target server in an open connection. These functions return the number of characters of data received through its RETURN statement after writing the specified number of bytes to the server (in the case of WRITE_RAW and WRITE_TEXT).

The WRITE_LINE function adds a NEWLINE character message before it is transmitted. The WRITE_TEXT function converts its data to the on-the-wire character set before the transmission.

The prototypes of these functions are shown below,

CLOSE_CONNECTION and CLOSE_ALL_CONNECTIONS Procedures

The CLOSE_CONNECTION and CLOSE_ALL_CONNECTIONS procedures close a single and all the connections respectively. A TCP/IP connection remains open until explicitly said. Failing to close the unwanted connections may result in holding up of the system resources unconditionally.

The prototypes of these procedures are shown below,

In the below example, a connection to GOOGLE server has been established using the OPEN_CONNECTION function with the default port number: 80 and character set as AL32UTF8. The HTTP request has been sent to the server using the WRITE_LINE function and the response is retrieved using the GET_LINE function within a loop. Finally, the connection has been terminated using the CLOSE_CONNECTION procedure after receiving the complete response from the server.

Results:

HTTP/1.0 302 Found

Cache-Control: private

Content-Type: text/html; charset=UTF-8

Location: http://www.google.co.in/?gfe_rd=cr&ei=lRrbV5zTMcWL8QfltbigBA

Content-Length: 261

Date: Thu, 15 Sep 2016 22:03:01 GMT

<HTML File>

---------------------------------------------------------------------

Here are the utl_tcp stored procedures:

Here are the utl_tcp functions: (a function must return a value)

The utl_tcp package provides PL/SQL programs the capability to communicate via TCP/IP (Transmission Control Protocol/Internet Protocol). The utl_tcp package requires that the Oracle XMLDB component was chosen during initial database creation or subsequent modification utilizing DBCA. Otherwise, the following series of error messages will come up:

Just like utl_smtp, this is one occasion where an example is worth more than the reference material. The email_myself_tcp_demo.sql PL/SQL code is a snippet of PL/SQL to email a message:

Next, examine the procedures and functions provided by the utl_tcp package. Key to the process is creating a TCP connection and then using that on subsequent utl_tcp calls. One also needs to work with the following data type and constants:

Close_data is a function that determines the number of bytes available for consumption from a TCP/IP connection and returns a pls_integer.

Close_all_connections is a utl_tcp procedure that simply closes all the open TCP/IP connections. It takes no parameters.

Close_connection is a  utl_tcp procedure that closes the specified open TCP/IP connection.

Flush is a  utl_tcp procedure that immediately transmits any and all data in the output buffer.

Get_line is a  utl_tcp function that reads a line of data and returns a VARCHAR2. The peek parameter allows one to look ahead in the buffer without actually consuming the message.

Get_line_nchar is a  utl_tcp function that reads a line of data, and returns a NVARCHAR2. The peek parameter allows one to look ahead in the buffer without actually consuming the message.

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.