Ora-29024 certificate validation failure


#1

Hi there,

I’m using utl_http in PL/SQL to call hubspot API. I have no problems when it’s a POST method but when I try a GET I always receive ora-29024 certificate validation failure. And the hubspot certificate is valid

The method I try to call is getcontactbyemail

Thanks for your help

Here’s the code I run

CREATE OR REPLACE FUNCTION JLR_WEBSITE.HUBSPOT_get_new_vid
RETURN NUMBER
IS
req UTL_HTTP.req;
res UTL_HTTP.resp;
url VARCHAR2 (500);

v_data VARCHAR2 (4000);
buffer CLOB;
v_content CLOB;
NB_UPD NUMBER := 0;
reqlength BINARY_INTEGER := 0;
amount PLS_INTEGER := 2000;
offset PLS_INTEGER := 1;
BEGIN
/*
pour que cette procedure roule bien j’ai du setter le ACL, nouveaute de la version 11, j’ai suivi la doc de ce site web :
https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
*/

FOR c IN (SELECT *
FROM vw_hb_user
WHERE USR_HB_VID IS NULL AND ROWNUM < 5)
LOOP
url :=
https://api.hubapi.com/contacts/v1/contact/email/
|| c.email
|| ‘/profile?hapikey=Mykey’;

  req := UTL_HTTP.begin_request (url, 'GET');
  UTL_HTTP.set_header (req, 'User-Agent', 'mozilla/4.0');
  UTL_HTTP.set_header (req, 'content-type', 'application/json');

  res := UTL_HTTP.get_response (req);

  send_mail ('no_reply@jlr.ca',
             'cboivin@jlr.ca',
             'hb',
             res.status_code);

  BEGIN
     v_data := NULL;

     LOOP
        UTL_HTTP.read_line (res, buffer);
        --DBMS_OUTPUT.put_line (buffer);
        v_data := v_data || buffer;

        send_mail (
           'no_reply@jlr.ca',
           'cboivin@jlr.ca',
           'hb',
           buffer || CHR (13) || CHR (13) || SUBSTR (v_content, 1, 100));
        UTL_HTTP.end_response (res);

        RETURN 1;
     END LOOP;

     UTL_HTTP.end_response (res);
  EXCEPTION
     WHEN UTL_HTTP.end_of_body
     THEN
        UTL_HTTP.end_response (res);
        RETURN 0;
  END;

END LOOP;
END;
/