How to Open TCP Connections with UTL_TCP

UTL_TCP is the lowest level of PL/SQL networking: it opens a plain TCP connection to a host and port and lets you write and read lines or bytes. UTL_HTTP and UTL_SMTP are built on it. Use it directly for simple text protocols that have no package of their own.

Code for This Guide

The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

c := utl_tcp.open_connection(remote_host, remote_port, charset => 'AL32UTF8',
                             tx_timeout => seconds);
n := utl_tcp.write_line(c, text);
utl_tcp.flush(c);
line := utl_tcp.get_line(c, remove_crlf => true);
utl_tcp.close_connection(c);

Reading after the server closes the connection raises UTL_TCP.END_OF_INPUT. The host and port need an access control entry with the connect privilege.

The examples talk to the local test server, setup/netlab/server.mjs in the repository.

Speak HTTP by Hand

Example:

declare
  c      utl_tcp.connection;
  v_n    pls_integer;
  v_line varchar2(1024);
begin
  c := utl_tcp.open_connection(remote_host => 'host.docker.internal', remote_port => 8099,
                               charset => 'AL32UTF8');
  v_n := utl_tcp.write_line(c, 'GET /notices HTTP/1.0');          -- HTTP, written by hand
  v_n := utl_tcp.write_line(c, 'Host: host.docker.internal');
  v_n := utl_tcp.write_line(c);
  utl_tcp.flush(c);
  begin
    loop
      v_line := utl_tcp.get_line(c, remove_crlf => true);
      if v_line is null or v_line not like 'Date:%' then    -- skip the changing date
        dbms_output.put_line(v_line);
      end if;
    end loop;
  exception
    when utl_tcp.end_of_input then                       -- the server closed the connection
      utl_tcp.close_connection(c);
  end;
end;
/

Output:

HTTP/1.1 200 OK
Content-Type: text/plain; charset=utf-8
Connection: close

Gate B12: boarding from 21:30
Lounge: open until 23:00
Wi-Fi: NimbusGuest

PL/SQL procedure successfully completed.

The block writes an HTTP request line, a Host header, and an empty line, then reads the raw response: status line, headers, an empty line, and the body. The Date header is skipped since it changes every run. END_OF_INPUT ends the loop when the server closes the connection.

Read a Server Greeting

Example:

declare
  c      utl_tcp.connection;
  v_n    pls_integer;
begin
  c := utl_tcp.open_connection('host.docker.internal', 2525, charset => 'AL32UTF8',
                               tx_timeout => 5);
  dbms_output.put_line('server says: ' || utl_tcp.get_line(c, remove_crlf => true));
  v_n := utl_tcp.write_line(c, 'QUIT');
  dbms_output.put_line('server says: ' || utl_tcp.get_line(c, remove_crlf => true));
  utl_tcp.close_connection(c);
end;
/

Output:

server says: 220 netlab ESMTP ready (test server, nothing is delivered)
server says: 221 2.0.0 bye

PL/SQL procedure successfully completed.

The SMTP port greets every new connection with a 220 line. The block reads it, sends QUIT, and reads the goodbye, the same exchange UTL_SMTP performs for you.

Things to Know

• Set TX_TIMEOUT so a server that does not answer cannot block the session forever.
• Use the character set the protocol expects; lines end with CR LF, which WRITE_LINE adds.
• Prefer UTL_HTTP and UTL_SMTP when the protocol is HTTP or mail.

Related Guides

• How to Read HTTP Responses with UTL_HTTP.BEGIN_REQUEST
• How to Allow Network Access with DBMS_NETWORK_ACL_ADMIN

Conclusion

UTL_TCP opens raw TCP connections from PL/SQL for writing and reading lines or bytes. Use it for simple text protocols, set a timeout, read until END_OF_INPUT, and close the connection.