Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

HTTPS Webservices Issue though PL/SQL

3216164Sep 30 2016 — edited Oct 1 2016

Experts,

Need help to resolve issues with accessing HTTPS webservices through PL/SQL

I'm on Oracle DB 12.1.0.2.

I did the below steps before accessing webservices through PL/SQL

orapki wallet create -wallet LABEL_GEN

orapki wallet add -wallet LABEL_GEN -trusted_cert -cert 'cert_file' -pwd password

orapki wallet add -wallet LABEL_GEN -trusted_cert -cert 'cert_file' -pwd password

orapki wallet add -wallet LABEL_GEN -trusted_cert -cert 'cert_file' -pwd password

orapki wallet display -wallet LABEL_GEN -pwd password

Code below worked perfectly fine when I did the above steps but it is failing after a disconnect/reconnect to the database session. Please check the code and error below.

DECLARE

lo_req  UTL_HTTP.req;

    lo_resp UTL_HTTP.resp;

    BEGIN

    UTL_HTTP.SET_WALLET ('file:WALLET_PATH','password');

    lo_req := UTL_HTTP.begin_request('https://..URL..');

    lo_resp := UTL_HTTP.get_response(lo_req);

    dbms_output.put_line(lo_resp.status_code);

    --dbms_output.put_line(lo_resp);

    UTL_HTTP.end_response(lo_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 6

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.

Any inputs to this would be highly appreciated!!!
This post has been answered by gdanby on Oct 1 2016
Jump to Answer

Comments

John Thorton

[oracle@vbgeneric dbs]$ oerr ora 29024

29024, 00000, "Certificate validation failure"

// *Cause:  The certificate sent by the other side could not be validated. This may occur if

//          the certificate has expired, has been revoked, or is invalid for another reason.

// *Action: Check the certificate to determine whether it is valid. Obtain a new certificate,

//          alert the sender that the certificate has failed, or resend.

[oracle@vbgeneric dbs]$ oerr ora 29273

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.

Oracle is the victim; not the culprit.

Root cause & fix are external to Oracle.

3216164

Hi John,

Thanks for your response.It was working when I imported all certificates and the issue appeared all the sudden when I reconnected the oracle database session. Certificates and valid and they have not been revoked. just wondering if I am missing anything else. I dont have any issues sending the request and getting the response from SOAP UI.

gdanby

I've tried it on Oracle 12.1.0.2 and your code works fine. I used the following "test" web service - https://fps.amazonaws.com/doc/2008-09-17/AmazonFPS.wsdl . I exported the root certificate as X.509 (PEM) to add to the wallet. (Edit: I was reading from an unverified source that the only parts of the certificate chain that should be added in Oracle 12 are the root and intermediary certificates and not the end user certificate)

How do you mean "reconnected the Oracle session", what are you using as a client ..?

3216164

Hi Gdanby,

Thanks for your response. In your test webservice (https://fps.amazonaws.com/doc/2008-09-17/AmazonFPS.wsdl , I can see three certificates (Verisign, Symantec and fps.amazon..). Did you add all the three to the wallet?

gdanby
Answer

I decided to delete my wallet and start again.

Step 1. Added Verisign (root certificate). Procedure worked, disconnected and reconnected and still worked.

Step 2. Added Symentec (intermediate certificate). Procedure worked, disconnected and reconnected and still worked.

Step 3. Added fps.amazon (user certificate). Procedure worked, disconnected and reconnected and FAILED with :

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 7

Unfortunately, I don't think you can just delete a certificate from the wallet. I've exported them before and deleted the entire wallet before adding the ones back in again I need.

Marked as Answer by 3216164 · Sep 27 2020
Vlad Visan-Oracle

You need to remove the user certificate. You only need the root and intermediate one, let me know the results

3216164

Thank you Gdanby for the detailed steps!!!! Adding just the root certificate did the trick!!! it is working perfectly fine now!! Thanks for all your help on this.

3216164

Adding just the root resolved the issue. Thanks for your inputs!!

1 person found this helpful
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 29 2016
Added on Sep 30 2016
#ssl
8 comments
11,839 views