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.
