Search Results close_connection

Overview

SYS.UTL_TCP is a server-side PL/SQL package that provides TCP/IP client-side access functionality within the Oracle E-Business Suite environment. Its purpose is to allow PL/SQL programs to initiate outbound connections to external TCP/IP services, exchange text or binary data with those services, and then terminate the connection. The package supports only connections initiated by the PL/SQL program itself; it cannot accept inbound connections originating from outside the program.

In the EBS 12.1.1 and 12.2.2 technical stack, UTL_TCP sits in the SYS schema and is classified as an OTHER API. It is not an EBS business API in the traditional sense, but rather a general-purpose network utility that enables integration scenarios such as retrieving content from web servers, communicating with third-party messaging endpoints, or exchanging data with external file-transfer and middleware services. Because it is a client-side-only API, it is frequently used in custom concurrent programs, database triggers, and PL/SQL integration routines where outbound TCP communication is required.

A meaningful implementation detail is that UTL_TCP is referenced by five other packages in the EBS data model. This indicates that the utility is embedded inside other PL/SQL units rather than always being called directly, which amplifies its operational importance: a failure or restriction in UTL_TCP can cascade into dependent packages.

Key Procedures and Functions

The package exposes seventeen documented procedures and functions. Connection lifecycle routines include OPEN_CONNECTION, which establishes a TCP/IP connection and returns a connection record, and SECURE_CONNECTION, which establishes an SSL-enabled connection. CLOSE_CONNECTION terminates a specific connection, while CLOSE_ALL_CONNECTIONS terminates all open connections in the session. FLUSH forces any buffered outbound data to be transmitted.

For binary data, READ_RAW and WRITE_RAW send and receive raw byte streams, while GET_RAW reads raw data and advances the read position. For textual communication, READ_TEXT, WRITE_TEXT, and GET_TEXT handle character data, with GET_TEXT_NCHAR providing national character set support. Line-oriented operations include READ_LINE, WRITE_LINE, and GET_LINE, with GET_LINE_NCHAR providing the corresponding national character set variant. The AVAILABLE function reports how many bytes or characters can be read without blocking. All connection functions operate against the connection record type, which carries attributes such as remote host, remote port, local host, local port, character set, newline sequence, and transfer timeout.

Tables Accessed

UTL_TCP does not reference any application tables, and no EBS tables are documented as being accessed through APPS synonyms. This is consistent with its role as a network transport utility: it reads from and writes to TCP/IP sockets rather than database tables. Any persistence of retrieved data is controlled entirely by the calling program, which must store results in its own structures or tables. Consequently, the package has no direct data model footprint and no table-level dependencies to manage.

Usage Notes

UTL_TCP is typically invoked from custom PL/SQL code, concurrent program logic, or integration routines rather than from standard EBS forms. A common pattern is to open a connection with OPEN_CONNECTION, write an outbound request, loop over GET_LINE or GET_RAW to read the response until an end_of_input exception is raised, and then close the connection. Because the caller controls persistence, integration programs must explicitly commit or store retrieved content.

When using the read_raw family, note that the metadata supports look-ahead via READ_RAW before data is formally consumed by GET_RAW. The transfer timeout attribute in the connection record should be set to avoid indefinite blocking. Network access from the database tier, firewall rules, and ACL configuration in later database releases all affect whether connections succeed, so these calls should be wrapped with exception handling for end_of_input and transport errors. In EBS environments, direct calls should be limited to trusted integration code, since the package operates with AUTHID CURRENT_USER and enables arbitrary outbound network communication.