Creating something through a REST API means sending a POST request with a JSON body. With UTL_HTTP, the request is opened with the POST method, the content type and length go in headers, and WRITE_TEXT writes the body before the response is read.
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
req := utl_http.begin_request(url, 'POST'); utl_http.set_header(req, 'Content-Type', 'application/json'); utl_http.set_header(req, 'Content-Length', lengthb(body)); utl_http.write_text(req, body); -- write_raw for binary bodies resp := utl_http.get_response(req);
The examples call the local test server, setup/netlab/server.mjs in the repository, which echoes the booking it receives.
Post a JSON Booking
Example:
declare
v_req utl_http.req;
v_resp utl_http.resp;
v_body varchar2(200) := json_object('flight' value 'NM150', 'passengers' value 2);
v_text varchar2(32767);
begin
v_req := utl_http.begin_request('http://host.docker.internal:8099/bookings', 'POST');
utl_http.set_header(v_req, 'Content-Type', 'application/json');
utl_http.set_header(v_req, 'Content-Length', lengthb(v_body));
utl_http.write_text(v_req, v_body);
v_resp := utl_http.get_response(v_req);
utl_http.read_text(v_resp, v_text);
utl_http.end_response(v_resp);
dbms_output.put_line(v_resp.status_code || ': ' || v_text);
dbms_output.put_line('booking reference: ' || json_value(v_text, '$.booking_ref'));
end;
/Output:
201: {"booking_ref":"NX7Q2P","received":{"flight":"NM150","passengers":2},"bytes":33}
booking reference: NX7Q2P
PL/SQL procedure successfully completed.JSON_OBJECT builds the body, and LENGTHB gives its length in bytes, 33. The server answers 201 Created with a booking reference, read with JSON_VALUE.
Handle a Rejected Request
Example:
declare
v_req utl_http.req;
v_resp utl_http.resp;
v_body varchar2(100) := '{"flight": NM150}'; -- not valid JSON
v_text varchar2(32767);
begin
v_req := utl_http.begin_request('http://host.docker.internal:8099/bookings', 'POST');
utl_http.set_header(v_req, 'Content-Type', 'application/json');
utl_http.set_header(v_req, 'Content-Length', lengthb(v_body));
utl_http.write_text(v_req, v_body);
v_resp := utl_http.get_response(v_req);
utl_http.read_text(v_resp, v_text);
utl_http.end_response(v_resp);
if v_resp.status_code >= 400 then
dbms_output.put_line('rejected with ' || v_resp.status_code || ': ' || v_text);
end if;
end;
/Output:
rejected with 400: {"error":"invalid JSON"}
PL/SQL procedure successfully completed.The body is not valid JSON, and the server rejects it with 400. The status code tells the code what happened, since UTL_HTTP does not raise an error for it.
Things to Know
- Content-Length is in bytes: use LENGTHB, not LENGTH, when the body has non-ASCII characters.
- For bodies over 32,767 bytes, call WRITE_TEXT several times, or set Transfer-Encoding: chunked instead of the length.
- Set the body character set with UTL_HTTP.SET_BODY_CHARSET, or in the Content-Type header, to send UTF-8.
Related Guides
Conclusion
A POST with UTL_HTTP opens the request with the POST method, sets the content type and byte length, writes the body with WRITE_TEXT, and reads the response. Build the body with JSON functions and check the status code of the answer.
