API Calling Using URL_HTTP
declare v_req utl_http.req;
v_res utl_http.resp;
v_buffer varchar2(4000);
v_body varchar2(4000) := '{"field":"value"}'; -- Your JSON
begin
-- Set connection.
v_req := utl_http.begin_request('http://yourapi_base_url/operation','POST');
utl_http.set_authentication(v_req, 'username','password');
utl_http.set_header(v_req, 'content-type', 'application/json');
utl_http.set_header(v_req, 'Content-Length', length(v_body));
-- Invoke REST API.
utl_http.write_text(v_req, v_body);
-- Get response.
v_res := utl_http.get_response(v_req);
begin
loop
utl_http.read_line(v_res, v_buffer);
-- Do something with buffer.
dbms_output.put_line(v_buffer);
end loop;
utl_http.end_response(v_res);
exception
when utl_http.end_of_body then
utl_http.end_response(v_res);
end;
end;
But if you have Oracle APEX installed, then you can try APEX_WEB_SERVICE package (much simpler).
declare
v_response clob;
v_buffer varchar2(32767);
v_buffer_size number := 32000;
v_offset number := 1;
begin
-- Set connection and invoke REST API.
v_response := apex_web_service.make_rest_request(
p_url => 'http://yourapi_base_url/operation',
p_http_method => 'POST',
p_username => 'username',
p_password => 'password',
p_body => '{"field":"value"}' -- Your JSON.
);
-- Get response.
begin
loop
dbms_lob.read(v_response, v_buffer_size, v_offset, v_buffer);
-- Do something with buffer.
DBMS_OUTPUT.PUT_LINE(v_buffer);
v_offset := v_offset + v_buffer_size;
end loop;
exception
when no_data_found then
null;
end;
end;
If you get an ACL exception, then you have to create ACL to open TCP port to connect with REST API.
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'acl.xml',
description => 'Connecting with REST API',
principal => 'YOUR_DATABASE_SCHEMA',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL
);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'acl.xml',
host => 'localhost', -- Or hostname of REST API server (e.g. "example.com").
lower_port => 80, -- For HTTPS put 443.
upper_port => NULL
);
COMMIT;
end;
I assume your REST API is protected by basic authentication scheme (user and password). To keep this example simple i used HTTP. If you have to connect via HTTPS, then you have to change TCP port in ACL and configure Oracle Wallet for your Oracle database instance.
