Skip to Main Content

Developer Community

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to call rest API from oracle procedures?

user-znh6dOct 14 2023

I want to call a rest API from oracle procedures. I tried with UTL_HTTP and it doesn't work still. kan anyone kindly help me to fix this issue. below code is calling https request get method.

1)create acl

BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('local_sx_acl_file.xml');
END;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'local_sx_acl_file.xml',
description => 'A test of the ACL functionality',
principal => 'AMILA',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'local_sx_acl_file.xml',
principal => 'AMILA',
is_grant => true,
privilege => 'resolve'
);
end;

begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'local_sx_acl_file.xml',
host => '*',
lower_port => 9002,
upper_port => NULL);
end;

  1. create procedure

SET SERVEROUTPUT ON SIZE 40000

DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
req := UTL_HTTP.BEGIN_REQUEST('https://byrtblo8c5.execute-api.ap-south-1.amazonaws.com/test/test');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;

Comments
Post Details
Added on Oct 14 2023
4 comments
2,024 views