4 Replies Latest reply on Jun 10, 2015 6:01 AM by Gerd S.

    UTL_HTTP - ORA-29024 Certificate validation failure

    Shra1

      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