I'm having issues with Oracle Certificate validation failure. while using UTL_HTTP calls. I've set the Oracle wallet path correctly and downloaded and registered the server certificates in Oracle Wallet. Tried similar https calls to other services. they're good. Can anyone throw ideas as to potential errors in the code/calls below?
CREATE OR REPLACE PROCEDURE show_html_from_url (p_url IN VARCHAR2 DEFAULT NULL) AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_text VARCHAR2(32767);
l_default_url VARCHAR2(300) := 'https://gb.redhat.com/';
l_url VARCHAR2(600) := 'https://global.blyeplod.net/blyapi/Contract/ProjectSetup';
l_un VARCHAR2(256) := 'username'; --sending correct username
l_pwd VARCHAR2(32) := 'password'; --sending correct password
l_api_key VARCHAR2(256) := '***';
lv_wallet_path VARCHAR2(300) := '/u01/app/oracle/product/11.1.0/db_1/wallet';
lv_request_body VARCHAR2(4000) :=
'{"Projects":{"Project":{"ProjectNumber":"110229","ProjectFriendlyName":"110229 - Test Project","ProjectRegion":"NAM"}}}';
BEGIN
if p_url is not null then
l_url := p_url;
end if;
-- Make a HTTP request and get the response.
UTL_HTTP.set_wallet('file:'||lv_wallet_path);
--//This step raises the ORA-29024: Certificate validation failure
l_http_request := UTL_HTTP.begin_request(l_url,'POST' --//Method
,'HTTP/1.1');
UTL_HTTP.set_authentication(l_http_request,l_un,l_pwd);
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml charset=UTF-8');
UTL_HTTP.set_header(l_http_request, 'Content-Length', length(lv_request_body));
UTL_HTTP.write_text(l_http_request, lv_request_body);
--//ping and check response
l_http_response := UTL_HTTP.get_response(l_http_request);
-- Loop through the response.
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
DBMS_OUTPUT.put_line (l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
UTL_TCP.close_all_connections;
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('***HTTP Test Raised error: '||sqlerrm);
UTL_HTTP.end_response(l_http_response);
UTL_TCP.close_all_connections;
RAISE;
END show_html_from_url;
/
SQL> exec show_html_from_url
***HTTP Test Raised error: ORA-29273: HTTP request failed
ORA-06512: at
"SYS.UTL_HTTP", line 1029
ORA-29024: Certificate validation failure
BEGIN show_html_from_url; END;
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1389
ORA-29261: bad argument
ORA-06512: at "PWB.SHOW_HTML_FROM_URL", line 41
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-29024: Certificate validation failure
ORA-06512: at line 1