I'm trying to make a call to a rest service using PLSQL from an Oracle 12c DB (locally installed), but I'm hitting the above certification error. I've spent a couple of days trying to resolve this through searches but so far I've not come up with an answer that works.
The call is to HTTPS so I require a certificate. I downloaded the certificate from the website and used Oracle Wallet Manager to create a new wallet in the location mentioned below with the password "Oracle123". I can reopen the wallet with this password so I know it's correct and wasn't a typo.
I have tried all multiple variations to downloading and inserting the certificates into the wallet. This includes exporting the cert in the 3 standard formats DER, Base64, and PCKS, and I have tried with only the Root, only the Intermediate, both Root and Intermediate, and the chain. I have the cert validation failure with each attempt.
Originally I used the 11g approach to creating ACLs at first, but after noting that many of the packages are deprecated, I have tried to stick with the new packages as below:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
HOST => 'api.mavenlink.com'
, ACE => XS$ACE_TYPE
(
PRIVILEGE_LIST => XS$NAME_LIST ( 'CONNECT' , 'RESOLVE' )
, PRINCIPAL_NAME => 'ODI_USER'
, PRINCIPAL_TYPE => XS_ACL.PTYPE_DB
)
) ;
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE
(
WALLET_PATH => 'file:D:\app\database\product\12.1.0\dbhome_1\BIN\wallets'
, ACE => XS$ACE_TYPE
(
PRIVILEGE_LIST => XS$NAME_LIST ( 'USE_CLIENT_CERTIFICATES' , 'USE_PASSWORDS' )
, PRINCIPAL_NAME => 'ODI_USER'
, PRINCIPAL_TYPE => XS_ACL.PTYPE_DB
)
) ;
END ;
/
-- PL/SQL procedure successfully completed.
I do not know if I have supplied the correct value for host, but I have also tried variations on this including *.xxxxxx.com , *.com and *
USER_HOST_ACES and USER_HOST_WALLETS both look as I expect.
The PLSQL block used to make the call is:
DECLARE
REQ_CONTEXT UTL_HTTP.REQUEST_CONTEXT_KEY ;
REQ UTL_HTTP.REQ ;
RESP UTL_HTTP.RESP ;
VAL VARCHAR2 ( 2000 ) ;
BEGIN
REQ_CONTEXT := UTL_HTTP.CREATE_REQUEST_CONTEXT
(
WALLET_PATH => 'file:D:\app\database\product\12.1.0\dbhome_1\BIN\wallets'
, WALLET_PASSWORD => 'Oracle123'
) ;
REQ := UTL_HTTP.BEGIN_REQUEST
(
URL => 'https://api.mavenlink.com/api/v1/custom_field_values?per_page=$1&page=$1'
, METHOD => 'GET'
, HTTP_VERSION => NULL
, REQUEST_CONTEXT => REQ_CONTEXT
) ;
UTL_HTTP.SET_HEADER ( REQ , 'Authorization' , 'Bearer ?????????????????' ) ;
UTL_HTTP.SET_HEADER ( REQ , 'content-type' , 'application/json' ) ;
RESP := UTL_HTTP.GET_RESPONSE ( REQ ) ;
LOOP
UTL_HTTP.READ_LINE ( RESP , VAL , TRUE ) ;
DBMS_OUTPUT.PUT_LINE ( VAL ) ;
END LOOP ;
UTL_HTTP.END_RESPONSE ( RESP ) ;
UTL_HTTP.DESTROY_REQUEST_CONTEXT ( REQ_CONTEXT ) ;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN UTL_HTTP.END_RESPONSE ( RESP ) ;
END ;
Error report -
ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at line 15
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.
The OS User has access to the wallet, and the password does not appear to be causing a problem.
I have previously been using PowerShell to connect to this API so I know service is correct.
Does anyone have any thoughts?