Definition:
In Oracle PL/SQL, UTL_TCP is a built in package which communicates with external TCP/IP based servers in internet based applications. Before the introduction of UTL_MAIL, UTL_TCP was the primary utility package to give TCP/IP connections through PL/SQL programs. It can also interact with the TCP/IP client side access functionality in PL/SQL.
The possible exceptions associated with the UTL_TCP package are listed below:
- BUFFER_TOO_SMALL - Indicates that the buffer size is too small.
- END_OF_INPUT - Indicates that the connection doesn't have sufficient input to read.
- NETWORK_ERROR - Raised when connection encounters network congestion.
- BAS_ARGUMENT - Raised when invalidated input is given to its subprogram.
- TRANSFER_TIMEOUT - Indicates that no data is available for reading in connection.
- PARTIAL_MULTIBYTE_ERROR - Raised when complete character data is missing.
The subprograms of the package are listed below, as per Oracle documentation.
1. CONNECTION - A PL/SQL record type used to represent a TCP/IP connection.
Example 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
);
2. CRLF - The character sequence carriage-return line-feed. It is the newline sequence commonly used many communication standards.
Example Syntax:
CRLF varchar2(10);
3. OPEN_CONNECTION Function - Opens a TCP/IP connection to a specified service.
Example 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;
4. AVAILABLE Function - Determines the number of bytes available for reading from a TCP/IP connection.
Example Syntax:
UTL_TCP.AVAILABLE (
c IN OUT NOCOPY connection,
timeout IN PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER;
5. READ_RAW Function - Receives binary data from a service on an open connection.
Example 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;
6. WRITE_RAW Function - Transmits a binary message to a service on an open connection.
Example Syntax:
UTL_TCP.WRITE_RAW (c IN OUT NOCOPY connection,
data IN RAW,
len IN PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
7. READ_TEXT Function - Receives text data from a service on an open connection.
Example 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;
8. WRITE_TEXT Function - Transmits a text message to a service on an open connection.
Example Syntax:
UTL_TCP.WRITE_TEXT (c IN OUT NOCOPY connection,
data IN VARCHAR2,
len IN PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
9. READ_LINE Function - Receives a text line from a service on an open connection.
Example 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;
10. WRITE_LINE Function - Transmits a text line to a service on an open connection.
Example Syntax:
UTL_TCP.WRITE_LINE (c IN OUT NOCOPY connection,
data IN VARCHAR2 DEFAULT NULL)
RETURN PLS_INTEGER;
11. 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.
Example 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;
12. FLUSH Procedure - Transmits all data in the output buffer, if a buffer is used, to the server immediately.
Example Syntax:
UTL_TCP.FLUSH (c IN OUT NOCOPY connection);
13. CLOSE_CONNECTION Procedure - Closes an open TCP/IP connection.
Example Syntax:
UTL_TCP.close_CLOSE_CONNECTION (c IN OUT NOCOPY connection);
14. CLOSE_ALL_CONNECTIONS Procedure - Closes all open TCP/IP connections.
Example Syntax:
UTL_TCP.CLOSE_ALL_CONNECTIONS;
Related Links: