I'm trying to post to a web API like it is done here but I can't figure out how, or who, to grant the ACL privileges to in an Oracle APEX environment. My code is hosted as a process under "After header":
declare
hmrcCode VARCHAR2(256);
req utl_http.req;
res utl_http.resp;
urlHMRC VARCHAR2(4000) := 'https://test-api.service.hmrc.gov.uk';
buffer varchar2(4000);
begin
hmrcCode := substr(owa_util.get_cgi_env('QUERY_STRING'), instr(owa_util.get_cgi_env('QUERY_STRING'), '&code') + 6);
urlHMRC := urlHMRC || '/hello/user' ||
'?client_secret=[secret]' ||
'&client_id=[clientID] ||
'&grant_type=authorization_code' ||
'&redirect_uri=' || APEX_PAGE.GET_URL() ||
'&code=' || hmrcCode;
req := utl_http.begin_request(urlHMRC, 'POST',' HTTP/1.1');
utl_http.set_header(req, 'Accept', 'application/vnd.hmrc.1.0+json');
utl_http.set_header(req, 'Authorization', 'Bearer ' || hmrcCode);
utl_http.write_text(req, NULL);
res := utl_http.get_response(req);
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer);
dbms_output.put_line(buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
end;
I get the following error:
ORA-29273: HTTP request failed
Contact your application administrator. Details about this incident are available via debug id "865307734".
Technical Info (only visible for developers)
- is_internal_error: false
- ora_sqlcode: -29273
- ora_sqlerrm: ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 93 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2501 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at line 18 ORA-06512: at "SYS.DBMS_SQL", line 1721 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1476 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC_LOCAL", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2503 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2538 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 76 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 1153 ORA-06512: at "APEX_190100.WWV_FLOW_PLUGIN", line 2457
- component.type: APEX_APPLICATION_PAGE_PROCESS
- component.id: 768181450885856202
- component.name: cptHelloWorld
- error_backtrace:
ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at line 18 ORA-06512: at "SYS.DBMS_SQL", line 1721 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 93 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2501 ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at line 18 ORA-06512: at "SYS.DBMS_SQL", line 1721 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486 ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1476 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC_LOCAL", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2503 ORA-06512: at "APEX_190100.WWV_FLOW_EXEC", line 2538 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 76 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS_NATIVE", line 1153 ORA-06512: at "APEX_190100.WWV_FLOW_PLUGIN", line 2457 ORA-06512: at "APEX_190100.WWV_FLOW_PROCESS", line 203
- error_statement:
begin declare hmrcCode VARCHAR2(256); req utl_http.req; res utl_http.resp; urlHMRC VARCHAR2(4000) := 'https://test-api.service.hmrc.gov.uk'; buffer varchar2(4000); begin hmrcCode := substr(owa_util.get_cgi_env('QUERY_STRING'), instr(owa_util.get_cgi_env('QUERY_STRING'), '&code') + 6); urlHMRC := urlHMRC || '/hello/user' || '?client_secret=[secret]' || '&client_id=[clientID]' || '&grant_type=authorization_code' || '&redirect_uri=' || APEX_PAGE.GET_URL() || '&code=' || hmrcCode; req := utl_http.begin_request(urlHMRC, 'POST',' HTTP/1.1'); utl_http.set_header(req, 'Accept', 'application/vnd.hmrc.1.0+json'); utl_http.set_header(req, 'Authorization', 'Bearer ' || hmrcCode); utl_http.write_text(req, NULL); res := utl_http.get_response(req); -- process the response from the HTTP call begin loop utl_http.read_line(res, buffer); dbms_output.put_line(buffer); end loop; utl_http.end_response(res); exception when utl_http.end_of_body then utl_http.end_response(res); end; end; end;
And that makes sense because I haven't granted anyone access, but just don't know who. Can anyone help, please?