In this article I want to explain what you need to do to be able to call Web Service.
Although there are many good articles on the web, no one provide complete step by step instructions on how to do it.
My goal in this article is to explain the complete procedure in a simple way.
In this case I’m calling SOAP Web Service that is out of my company network.
I assume you are making call to https resources (Web Service), first you need to check if port 443 (https) is open between Db server and the Web Service.
To check, from the Db server (profile oracle Db owner) execute the following command:
[oracle@firstname.lastname@example.org:~] > telnet soap.webservice.com 443
where instead of soap.webservice.com you’ll set you server name where the Web Service is running.
Opening ports from the database server is never good practice from the security perspective, but this is the mandatory step if you want to make a call from the Oracle Db.
In practice, you’ll call Web Service most likely from the application (either client or from the application server).
You need to download certificates from the Web Service.
To get certificate, use your browser (Firefox is always the best), go to the URL where is Web Service you want to call, and click the lock icon in the address bar, then More Information, and then View Certificate. Click on the details tab, and finally click Export button.
You can get more details in the following link:
Next step is to upload certificate file to the Db server, into directory where the Db owner (usually oracle) user have rw access rights.
scp webservice.crt username@webservice-host:/path/to/directory
With the Db owner profile (with set all ORA env variables) you need to create the wallet as this is needed to handle trusted certificates.
orapki wallet create -wallet /opt/rtl/oracle/product/11/wallet -pwd WalletPassword
Now you need to add uploaded certificate to the wallet.
orapki wallet add -wallet /opt/rtl/oracle/product/11/wallet -trusted_cert -cert “/tmp/GoDaddyRootCertificateAuthority.crt” -pwd WalletPassword
Last configuration step is to create ACL (Access List).
From 11g Db version, Oracle introduced ACL (Access Control List) for accessing external network services. Before using Web Service, you’ll need to create ACL.
The following example is for 11g version:
As a sysdba execute the following command:
exec dbms_network_acl_admin.create_acl(acl => 'test_acl_webservice.xml', description => 'Test', principal => 'SCOTT', is_grant => true, privilege => 'connect'); commit;
You can use the following command for test environment as it is security leak:
exec dbms_network_acl_admin.assign_acl(acl => 'test_acl_webservice.xml', host => '*', lower_port => 1, upper_port => 9999); commit;
On production servers (if you must use Web Service, see my previous observation regarding security) you are going to execute the following command:
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'test_acl_webservice.xml', host => 'webservice.hostname.com', lower_port => 443, upper_port => 443); commit;
Check if ACL list has been created:
select * from dba_network_acls where acl like '%test_acl_webservice.xml'; select * from dba_network_acl_privileges where principal = 'SCOTT';
With this step, setup has been completed.
Now you need to test if you can successfully use the web service.
You can find in this article details of how to do it:
Basically, I’m using show_html_from_url from the article above (or create my Java Stored Procedure even better), and then from SQL*Plus or SQL Developer:
SET SERVEROUTPUT ON exec utl_http.set_wallet('file:/opt/rtl/oracle/product/11/wallet', 'WalletPassword'); exec show_html_from_url('https://web-service-address', 'username', 'password');
Paste the output into the empty html file and open it in Web browser to get nice output.