SQL & PL/SQL

Hi all

I am developing an application in which I need to access API via https, but I have problems in the initial configuration.

I have the following configuration to do tests:

Ora-DB 18c XE, and Ora-DB 12c the problem are the same.

1.- Procedure show_html_from_url

create or replace PROCEDURE show_html_from_url (

  p_url  IN  VARCHAR2,

  p_username IN VARCHAR2 DEFAULT NULL,

  p_password IN VARCHAR2 DEFAULT NULL

) AS

  request_context UTL_HTTP.REQUEST_CONTEXT_KEY;

  l_http_request   UTL_HTTP.req;

  l_http_response  UTL_HTTP.resp;

  l_text           VARCHAR2(32767);

BEGIN

-- Make a HTTP request and get the response.

  l_http_request  := UTL_HTTP.begin_request(url => p_url);

  -- Use basic authentication if required.

  IF p_username IS NOT NULL and p_password IS NOT NULL THEN

    UTL_HTTP.set_authentication(l_http_request, p_username, p_password);

  END IF;

  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);

      insert into seguimiento

        (comentario_clob)

      values

        (l_text);

--      DBMS_OUTPUT.put_line (l_text);

    END LOOP;

  EXCEPTION

    WHEN UTL_HTTP.end_of_body THEN

      UTL_HTTP.end_response(l_http_response);

  END;

EXCEPTION

  WHEN OTHERS THEN

    UTL_HTTP.end_response(l_http_response);

    RAISE;

END show_html_from_url;

2.- oracle Wallet in the AWS EC2:

[oracle@xxxxxx redhat]$ pwd

/home/oracle/ssl/redhat

[oracle@xxxxxx  redhat]$ ll

total 28

-rw-------. 1 oracle oinstall 4533 Jul 25 13:11 cwallet.sso

-rw-------. 1 oracle oinstall    0 Jul 25 13:01 cwallet.sso.lck

-rwxrwx---. 1 oracle oinstall 1390 Jul 25 13:08 DigiCert_High_Assurance_EV_Root_CA.cer

-rwxrwx---. 1 oracle oinstall 1724 Jul 25 13:08 DigiCert_SHA2_Extended_Validation_Server_CA.cer

-rw-------. 1 oracle oinstall 4488 Jul 25 13:11 ewallet.p12

-rw-------. 1 oracle oinstall    0 Jul 25 13:01 ewallet.p12.lck

-rwxrwx---. 1 oracle oinstall 2636 Jul 25 13:08 www.redhat.com.cer

[oracle@xxxxxx redhat]$ orapki wallet display -wallet .

Oracle PKI Tool Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:

User Certificates:

Trusted Certificates:

Subject:        CN=DigiCert High Assurance EV Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US

Subject:        CN=www.redhat.com,OU=IT,O=Red Hat\, Inc.,L=Raleigh,ST=North Carolina,C=US,SERIALNUMBER=2945436,1.3.6.1.4.1.311.60.2.1.2=Delaware,1.3.6.1.4.1.311.60.2.1.3=US,2.5.4.15=Private Organization

Subject:        CN=DigiCert SHA2 Extended Validation Server CA,OU=www.digicert.com,O=DigiCert Inc,C=US

[oracle@xxxx redhat]$ orapki wallet export -wallet . -dn 'CN=DigiCert High Assurance EV Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US' -cert certificate1.cer

Oracle PKI Tool Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

[oracle@xxxx redhat]$ more certificate1.cer

-----BEGIN CERTIFICATE-----

MIIDxTCCAq2gAwIBAgIQAqxcJmoLQJuPC3nyrkYldzANBgkqhkiG9w0BAQUFADBs

MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3

d3cuZGlnaWNlcnQuY29tMSswKQYDVQQDEyJEaWdpQ2VydCBIaWdoIEFzc3VyYW5j

ZSBFViBSb290IENBMB4XDTA2MTExMDAwMDAwMFoXDTMxMTExMDAwMDAwMFowbDEL

MAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQd3d3

LmRpZ2ljZXJ0LmNvbTErMCkGA1UEAxMiRGlnaUNlcnQgSGlnaCBBc3N1cmFuY2Ug

RVYgUm9vdCBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMbM5XPm

+9S75S0tMqbf5YE/yc0lSbZxKsPVlDRnogocsF9ppkCxxLeyj9CYpKlBWTrT3JTW

PNt0OKRKzE0lgvdKpVMSOO7zSW1xkX5jtqumX8OkhPhPYlG++MXs2ziS4wblCJEM

xChBVfvLWokVfnHoNb9Ncgk9vjo4UFt3MRuNs8ckRZqnrG0AFFoEt7oT61EKmEFB

Ik5lYYeBQVCmeVyJ3hlKV9Uu5l0cUyx+mM0aBhakaHPQNAQTXKFx01p8VdteZOE3

hzBWBOURtCmAEvF5OYiiAhF8J2a3iLd48soKqDirCmTCv2ZdlYTBoSUeh10aUAsg

EsxBu24LUTi4S8sCAwEAAaNjMGEwDgYDVR0PAQH/BAQDAgGGMA8GA1UdEwEB/wQF

MAMBAf8wHQYDVR0OBBYEFLE+w2kD+L9HAdSYJhoIAu9jZCvDMB8GA1UdIwQYMBaA

FLE+w2kD+L9HAdSYJhoIAu9jZCvDMA0GCSqGSIb3DQEBBQUAA4IBAQAcGgaX3Nec

nzyIZgYIVyHbIUf4KmeqvxgydkAQV8GK83rZEWWONfqe/EW1ntlMMUu4kehDLI6z

eM7b41N5cdblIZQB2lWHmiRk9opmzN6cN82oNLFpmyPInngiK3BD41VHMWEZ71jF

hS9OMPagMRYjyOfiZRYzy78aG6A9+MpeizGLYAiJLQwGXFK3xPkKmNEVX58Svnw2

Yzi9RKR/5CYrCsSXaQ3pjOLAEFe4yHYSkVXySGnYvCoCWw9E1CAx2/S6cCZdkGCe

vEsXCS+0yx5DaMkHJ8HSXPfqIbloEpw8nL+e/IBcm2PN7EeqJSdnoDfzAIJ9VNep

+OkuE6N36B9K

-----END CERTIFICATE-----

[oracle@xxxxxx redhat]$ orapki wallet export -wallet . -dn 'CN=www.redhat.com,OU=IT,O=Red Hat\, Inc.,L=Raleigh,ST=North Carolina,C=US,SERIALNUMBER=2945436,1.3.6.1.4.1.311.60.2.1.2=Delaware,1.3.6.1.4.1.311.60.2.1.3=US,2.5.4.15=Private Organization' -cert certificate2.cer

Oracle PKI Tool Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

[oracle@xxxxxx redhat]$ more certificate2.cer

-----BEGIN CERTIFICATE-----

MIIHTjCCBjagAwIBAgIQCKHZUQafG1ivUnpN3nJUzzANBgkqhkiG9w0BAQsFADB1

MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3

d3cuZGlnaWNlcnQuY29tMTQwMgYDVQQDEytEaWdpQ2VydCBTSEEyIEV4dGVuZGVk

IFZhbGlkYXRpb24gU2VydmVyIENBMB4XDTE4MDMyMTAwMDAwMFoXDTIwMDMyMDEy

MDAwMFowgdcxHTAbBgNVBA8MFFByaXZhdGUgT3JnYW5pemF0aW9uMRMwEQYLKwYB

BAGCNzwCAQMTAlVTMRkwFwYLKwYBBAGCNzwCAQITCERlbGF3YXJlMRAwDgYDVQQF

EwcyOTQ1NDM2MQswCQYDVQQGEwJVUzEXMBUGA1UECBMOTm9ydGggQ2Fyb2xpbmEx

EDAOBgNVBAcTB1JhbGVpZ2gxFjAUBgNVBAoTDVJlZCBIYXQsIEluYy4xCzAJBgNV

BAsTAklUMRcwFQYDVQQDEw53d3cucmVkaGF0LmNvbTCCASIwDQYJKoZIhvcNAQEB

BQADggEPADCCAQoCggEBAKmnYSl351xFp3sh0JbGJZRh+8yQDLSdDtUEOmUnaFGD

ds2CVyoTNmlv85AK28yhV4en9Q32YzFx/SVbidhl7P19uBvP/QAHoe2Yk7ZZ9lBl

T+9Gy2zeZOMMYAPu/uG+wNDFVEAG3/gifF97IM26W3WOOlyqa3lfFgmbPOlBv6sD

VWMoZ+Zap0I9pbniIpboJMjHNUpVMvPKChk3oXhDMsSK3mf/pPyWFFtscha8Nbd3

kjOnHwr5wu2DlubtUerr4pxCzoklvkUkoZ64z/Ea4Gk0GFUUmZyUivDRsEONzcc+

wvKx5EOl39JHoApK8jgKh3j5qL7+Pjubuar8IbDa3fsCAwEAAaOCA3UwggNxMB8G

A1UdIwQYMBaAFD3TUKXWoK3u80pgCmXTIdT4+NYPMB0GA1UdDgQWBBRfBicRZhUO

oiLVJNQ6IqBRozoNJDAlBgNVHREEHjAcgg53d3cucmVkaGF0LmNvbYIKcmVkaGF0

LmNvbTAOBgNVHQ8BAf8EBAMCBaAwHQYDVR0lBBYwFAYIKwYBBQUHAwEGCCsGAQUF

BwMCMHUGA1UdHwRuMGwwNKAyoDCGLmh0dHA6Ly9jcmwzLmRpZ2ljZXJ0LmNvbS9z

aGEyLWV2LXNlcnZlci1nMi5jcmwwNKAyoDCGLmh0dHA6Ly9jcmw0LmRpZ2ljZXJ0

LmNvbS9zaGEyLWV2LXNlcnZlci1nMi5jcmwwSwYDVR0gBEQwQjA3BglghkgBhv1s

AgEwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAH

BgVngQwBATCBiAYIKwYBBQUHAQEEfDB6MCQGCCsGAQUFBzABhhhodHRwOi8vb2Nz

cC5kaWdpY2VydC5jb20wUgYIKwYBBQUHMAKGRmh0dHA6Ly9jYWNlcnRzLmRpZ2lj

ZXJ0LmNvbS9EaWdpQ2VydFNIQTJFeHRlbmRlZFZhbGlkYXRpb25TZXJ2ZXJDQS5j

cnQwCQYDVR0TBAIwADCCAX0GCisGAQQB1nkCBAIEggFtBIIBaQFnAHUApLkJkLQY

WBSHuxOizGdwCjw1mAT5G9+443fNDsgN3BAAAAFiSiRZgQAABAMARjBEAiBFtAW0

yUii4dprdZGvOgU4Dxe5dqETR5iUf4MVaR6w3QIgLEEi7qm6tLilae275hXsVnwx

owze9FlYwSun5sjx3JoAdgBWFAaaL9fC7NP14b1Esj7HRna5vJkRXMDvlJhV1onQ

3QAAAWJKJFqqAAAEAwBHMEUCIQDUtJC9QC/DsCwrni0hIV+TZKUAMaMnA7PBt97n

GL9kgwIgWyPdnUs5a8lRHxAzNjNseLbVP09Pi3kBbkCjU+zI/EAAdgC72d+8H4px

tZOUI5eqkntHOFeVCqtS6BqQlmQ2jh7RhQAAAWJKJFpQAAAEAwBHMEUCICFK7q0I

Z6/8Qc1borjKBNNyuZc9vQ2ReLEgTIypKL32AiEAr/W/UIEgy3ajbXphfq9uL9pK

Bxd43BzQhc2pQhWbdYMwDQYJKoZIhvcNAQELBQADggEBAKmQ5YNnKz7EIABTWGoR

H7Yg0nNDmjYnYyOIl/zsbPaVntQOGBzjK+IgrLL7nvpwUD4kDjzA34zzSQ/utYV4

omjXAMxruXZ1e174qNVkUla2vvO0+KzJXeAAOoXn8gFQCu6nUN29Yu+ZQQFPjqKV

0JMqj0tL3nko7VE+JB+lnK6vuMDNFQ6f6zXjBBhODsvMBgEbtN0qWLu/ptCP2DFQ

NBJLSU6P4SRXxd1LPWaSHyJXU4C48ORsMONGneRJ9GeZmx++qZ5H0apVTnciGOVy

ub+KhI6GgJ47G3qvo1kbz+HdaFick77oeJ0g6xCYBnUzfolVqYx07Y3RCw0vVmAc

Gyo=

-----END CERTIFICATE-----

Operation is successfully completed.

[oracle@xxxxx redhat]$ orapki wallet export -wallet . -dn 'CN=DigiCert SHA2 Extended Validation Server CA,OU=www.digicert.com,O=DigiCert Inc,C=US' -cert certificate3.cer

Oracle PKI Tool Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

[oracle@xxxxxx redhat]$ more certificate3.cer

-----BEGIN CERTIFICATE-----

MIIEtjCCA56gAwIBAgIQDHmpRLCMEZUgkmFf4msdgzANBgkqhkiG9w0BAQsFADBs

MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3

d3cuZGlnaWNlcnQuY29tMSswKQYDVQQDEyJEaWdpQ2VydCBIaWdoIEFzc3VyYW5j

ZSBFViBSb290IENBMB4XDTEzMTAyMjEyMDAwMFoXDTI4MTAyMjEyMDAwMFowdTEL

MAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQd3d3

LmRpZ2ljZXJ0LmNvbTE0MDIGA1UEAxMrRGlnaUNlcnQgU0hBMiBFeHRlbmRlZCBW

YWxpZGF0aW9uIFNlcnZlciBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoC

ggEBANdTpARR+JmmFkhLZyeqk0nQOe0MsLAAh/FnKIaFjI5j2ryxQDji0/XspQUY

uD0+xZkXMuwYjPrxDKZkIYXLBxA0sFKIKx9om9KxjxKws9LniB8f7zh3VFNfgHk/

LhqqqB5LKw2rt2O5Nbd9FLxZS99RStKh4gzikIKHaq7q12TWmFXo/a8aUGxUvBHy

/Urynbt/DvTVvo4WiRJV2MBxNO723C3sxIclho3YIeSwTQyJ3DkmF93215SF2AQh

cJ1vb/9cuhnhRctWVyh+HA1BV6q3uCe7seT6Ku8hI3UarS2bhjWMnHe1c63YlC3k

8wyd7sFOYn4XwHGeLN7x+RAoGTMCAwEAAaOCAUkwggFFMBIGA1UdEwEB/wQIMAYB

Af8CAQAwDgYDVR0PAQH/BAQDAgGGMB0GA1UdJQQWMBQGCCsGAQUFBwMBBggrBgEF

BQcDAjA0BggrBgEFBQcBAQQoMCYwJAYIKwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRp

Z2ljZXJ0LmNvbTBLBgNVHR8ERDBCMECgPqA8hjpodHRwOi8vY3JsNC5kaWdpY2Vy

dC5jb20vRGlnaUNlcnRIaWdoQXNzdXJhbmNlRVZSb290Q0EuY3JsMD0GA1UdIAQ2

MDQwMgYEVR0gADAqMCgGCCsGAQUFBwIBFhxodHRwczovL3d3dy5kaWdpY2VydC5j

b20vQ1BTMB0GA1UdDgQWBBQ901Cl1qCt7vNKYApl0yHU+PjWDzAfBgNVHSMEGDAW

gBSxPsNpA/i/RwHUmCYaCALvY2QrwzANBgkqhkiG9w0BAQsFAAOCAQEAnbbQkIbh

hgLtxaDwNBx0wY12zIYKqPBKikLWP8ipTa18CK3mtlC4ohpNiAexKSHc59rGPCHg

4xFJcKx6HQGkyhE6V6t9VypAdP3THYUYUN9XR3WhfVUgLkc3UHKMf4Ib0mKPLQNa

2sPIoc4sUqIAY+tzunHISScjl2SFnjgOrWNoPLpSgVh5oywM395t6zHyuqB8bPEs

1OG9d4Q3A84ytciagRpKkk47RpqF/oOi+Z6Mo8wNXrM9zwR4jxQUezKcxwCmXMS1

oVWNWlZopCJwqjyBcdmdqEU79OX2olHdx3ti6G8MdOu42vi/hw15UJGQmxg7kVkn

8TUoE6smftX3eg==

-----END CERTIFICATE-----

3.- Security AWS EC2

Inbound

Outbound

4.- ACL LIST

in Sql Developer:

select * from dba_network_acls;

5.- http test

EXEC show_html_from_url('http://www.tecnoambiente.cl');

Procedimiento PL/SQL terminado correctamente.

6.- httpS test

EXEC UTL_HTTP.set_wallet('file:/home/oracle/ssl/redhat', NULL);

EXEC show_html_from_url('https://www.redhat.com/en');

Procedimiento PL/SQL terminado correctamente.

Error que empieza en la línea: 2 del comando :

BEGIN show_html_from_url('https://www.redhat.com/en'); END;

Informe de error -

ORA-29273: fallo de la solicitud HTTP

ORA-06512: en "xxxxxx.SHOW_HTML_FROM_URL", línea 40

ORA-29024: Fallo de validación de certificado

ORA-06512: en "SYS.UTL_HTTP", línea 380

ORA-06512: en "SYS.UTL_HTTP", línea 1148

ORA-06512: en "xxxxxx.SHOW_HTML_FROM_URL", línea 13

ORA-06512: en línea 1

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.

apparently everything is well configured but delivery "ora-29024 certificate validation failure".

Your help in the solution is greatly appreciated.

thank you very much in advance

Carlos