Skip to Main Content
  • Questions

  • Making APEX_WEB_SERVICE.make_rest_request work with HTTPS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: March 07, 2018 - 6:46 pm UTC

Last updated: March 09, 2018 - 2:24 am UTC

Version: 5.0 in Oracle 12c R2

Viewed 10K+ times! This question is

You Asked

I have created a standard template database via DBCA in Oracle 12c R2 and then installed the APEX code via the apex installation directory that was installed with the Oracle 12c R2 binaries.

I have also upgraded APEX to 5.0.4

I have created a simple PL/SQL block that uses DBMS_NETWORK_ACL_ADMIN.append_host_ace to create and ACL and then creates a procedure that calls this website http://oracle-base.com/webservices/add-numbers.php via the APEX_WEB_SERVICE.make_rest_request routing.

I then call the procedure from SQL*Plus and it works as expected.

BUT

If I try to call https://oracle-base.com/webservices/add-numbers.php via the same routine it fails.

I have created a wallet and added the wallet path and wallet password to the call as shown below

l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => ' https://oracle-base.com/webservices/add-numbers.php',
p_http_method => 'GET',
p_parm_name => APEX_UTIL.string_to_table('p_int_1:p_int_2'),
p_parm_value => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2),
p_wallet_path => 'file:/home/oracle/Wallet',
p_wallet_pwd => 'S3-Oracle'
);

And I have also tried using the following code in the PL/SQL block

DBMS_NETWORK_ACL_ADMIN.append_wallet_ace
(
wallet_path => 'file:/home/oracle/Wallet',
ace => xs$ace_type
(
privilege_list => xs$name_list('use_client_certificates'),
principal_name => l_principal,
principal_type => xs_acl.ptype_db
)
);

However when I call the procedure from SQL*Plus all I get is ORA-29024: Certificate validation failure

So I am guessing that I am missing a step somewhere, but I have followed all the guides and My Oracle Support notes that I can find on the subject

Help appreciated thanks

Mike



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_GDG1FQ5LL7KYC678HIW9YF6QJ.html

and Connor said...

Since you're using Tim's example, don't forget this step:

https://oracle-base.com/articles/misc/utl_http-and-ssl#get-site-certificates

and then add the certificates to the wallet. That is the most common cause of certificate failure errors.




Rating

  (1 rating)

Comments are currently disabled for this question.

Comments

Saying I won't answer it is not an answer

Mike Revitt, March 08, 2018 - 8:42 am UTC

The answer was written by someone who clearly had not read the question and told me to perform a task that I had described in the question.

I can accept that you won't answer specific questions, although I question the value of this then because you used. But don't try to fob me off with worthless responses
Connor McDonald
March 09, 2018 - 2:23 am UTC

Would you like to elaborate exactly *where* in your original question you detailed where you added the site certificates ?

I have created a standard template database via DBCA in Oracle 12c R2 and then installed the APEX code via the apex installation directory that was installed with the Oracle 12c R2 binaries.


Nope.

I have also upgraded APEX to 5.0.4


Nope.

I have created a simple PL/SQL block that uses DBMS_NETWORK_ACL_ADMIN.append_host_ace to create and ACL and then creates a procedure that calls this website http://oracle-base.com/webservices/add-numbers.php via the APEX_WEB_SERVICE.make_rest_request routing.


Nope.

I then call the procedure from SQL*Plus and it works as expected.


Nope.

If I try to call https://oracle-base.com/webservices/add-numbers.php via the same routine it fails.


Nope.

I have created a wallet and added the wallet path and wallet password to the call as shown below

l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => ' https://oracle-base.com/webservices/add-numbers.php',
p_http_method => 'GET',
p_parm_name => APEX_UTIL.string_to_table('p_int_1:p_int_2'),
p_parm_value => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2),
p_wallet_path => 'file:/home/oracle/Wallet',
p_wallet_pwd => 'S3-Oracle'
);


Nope.

And I have also tried using the following code in the PL/SQL block

DBMS_NETWORK_ACL_ADMIN.append_wallet_ace
(
wallet_path => 'file:/home/oracle/Wallet',
ace => xs$ace_type
(
privilege_list => xs$name_list('use_client_certificates'),
principal_name => l_principal,
principal_type => xs_acl.ptype_db
)
);


Nope.

However when I call the procedure from SQL*Plus all I get is ORA-29024: Certificate validation failure

So I am guessing that I am missing a step somewhere, but I have followed all the guides and My Oracle Support notes that I can find on the subject

Help appreciated thanks

Mike

Nope.


Then you suspected that you were "missing a step". And since you made *no mention* of extracting site certificates and adding them to the wallet, this would seem a logical response from us to make sure you had not skipped it.

But you opted for vitriol.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.