I'd like to query a web service like https://nominatim.openstreetmap.org in a PL/SQL procedure.
I downloaded the certificates of this website with an internet browser and importet them into an Oracle wallet with orapki
Now my wallet looks like this:
C:\>orapki wallet display -wallet C:\app\oracle\product\18.0.0\dbhomeXE\owm
Oracle PKI Tool Release 18.0.0.0.0 - Production
18.1.0.0.0 : Version {1}
Copyright (c) 2004, 2017, Oracle und/oder der verbundenen Unternehmen. All rights reserved. Alle Rechte vorbehalten.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=nominatim.openstreetmap.org
Subject: CN=Let's Encrypt Authority X3,O=Let's Encrypt,C=US
Subject: CN=DST Root CA X3,O=Digital Signature Trust Co.
But whe I try to make a request to the website I allways get an ORA-29024 errror
set define off;
declare json_result varchar2(4000);
begin
json_result := utl_http.request('https://nominatim.openstreetmap.org/search?q=Unter%20den%20Linden%204+Berlin&format=geojson',NULL,'file:C:\app\oracle\product\18.0.0\dbhomeXE\owm', 'Password');
end;
/
gives me
Fehlerbericht -
ORA-29273: HTTP-Anforderung nicht erfolgreich
ORA-06512: in "SYS.UTL_HTTP", Zeile 1530
ORA-29024: Zertifikatvalidierung nicht erfolgreich
ORA-06512: in "SYS.UTL_HTTP", Zeile 380
ORA-06512: in "SYS.UTL_HTTP", Zeile 1470
ORA-06512: in Zeile 5
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
Where is my mistake? This was done with Oracle 18XE running on Windiows 10. But I have the same problem with an Oracle 12.2 SE2.