Hi folks,

I would love to have the JSON from the following REST request:

https://sgx.geodatenzentrum.de/geoserver/vg250-ew/wfs?service=wfs&version=2.0.0&request=GetFeature&typenames=vg250-ew:vg250_sta&outputFormat=json&srsName=urn:ogc:def:crs:EPSG::4326

This works fine with my browser or in Postman.
To get the JSON in PL/SQL I wrote the following:

declare
  lParamNames     apex_application_global.vc_arr2;
  lParamValues    apex_application_global.vc_arr2;
  lResponseAsClob clob;
begin
  lParamNames(lParamNames.count + 1)    := 'service';
  lParamValues(lParamValues.count + 1)  := 'wfs';
  
  lParamNames(lParamNames.count + 1)    := 'version';
  lParamValues(lParamValues.count + 1)  := '2.0.0';
  
  lParamNames(lParamNames.count + 1)    := 'request';
  lParamValues(lParamValues.count + 1)  := 'GetFeature';
  
  lParamNames(lParamNames.count + 1)    := 'typenames';
  lParamValues(lParamValues.count + 1)  := 'vg250-ew:vg250_sta';
  
  lParamNames(lParamNames.count + 1)    := 'outputFormat';
  lParamValues(lParamValues.count + 1)  := 'json';
  
  lParamNames(lParamNames.count + 1)    := 'srsName';
  lParamValues(lParamValues.count + 1)  := 'urn:ogc:def:crs:EPSG::4326';
    
  APEX_WEB_SERVICE.clear_request_headers();
  begin
    lResponseAsClob := APEX_WEB_SERVICE.make_rest_request(p_url => 'https://sgx.geodatenzentrum.de/geoserver/vg250-ew/wfs',
                                                          p_http_method => 'GET', 
                                                          p_parm_name => lParamNames, 
                                                          p_parm_value => lParamValues);
  exception
    when others then
      dbms_output.put_line('Error "' || sqlerrm || '" when requesting feature from webservice.');
  end;
  dbms_output.put_line('got return code ' || nvl(APEX_WEB_SERVICE.g_status_code, -1));
  if (APEX_WEB_SERVICE.g_status_code = 200) then
    dbms_output.put_line(lResponseAsClob);
  end if;
end;
/

The DBMS_OUTPUT shows

Error "ORA-29273: HTTP request failed" when requesting feature from webservice.

.

Deeper in the Error Stack I could find

ORA-29024: Certificate validation failure

I also appended the ACL as ADMIN with the following PL/SQL:

begin
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'sgx.geodatenzentrum.de',
         ace =>  xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'ENIGNUM',
                             principal_type => xs_acl.ptype_db));
end;
/

And I also checked the ACLs with the the following SQL statement:

select acl.host, acl.acl, acl.aclid, acl.acl_owner, pri.principal, pri.privilege, pri.is_grant, pri.invert
  from dba_network_acls acl
  left join dba_network_acl_privileges pri
    on pri.acl = acl.acl
  where acl.host like '%geodaten%'

. It shows

HOST                | ACL                                         | ACLID            | ACL_OWNER | PRINCIPAL | PRIVILEGE | IS_GRANT | INVERT 
sgx.geodatenzentrum.de | NETWORK_ACL_40A1CE1575E90893E0637F19000A0F27 | 000000008000279D | SYS       | ENIGNUM   | http | true | false

What do I need to do to call the upper named url successfully?

Thank you in advance.

Knut

    lResponseAsClob := APEX_WEB_SERVICE.make_rest_request(p_url => 'https://sgx.geodatenzentrum.de/geoserver/vg250-ew/wfs',
                                                          p_http_method => 'GET', 
                                                          p_parm_name => lParamNames, 
                                                          p_parm_value => lParamValues,
                                                          p_wallet_path=>'system:'
);