PL/SQL : Securely Connect to HTTPS URL using oracle wallet

Assumption :

You can execute the PL/SQL with the http connection successfully.


Step1 :

Get the Site Certificate :

Open the site in the browser, click on the Lock icon and then certificate information, under the certification path click on the root certificate, and do a copy to file with Base-64 encoded format

name this as root.cer

Do the same thing for intermediate certificate and name it as intermediate.cer


Step 2 :

Create a directory for wallet :

 mkdir -p /u01/app/oracle/admin/DBNAME/wallet  

Step 3:

create new wallet

 $ orapki wallet create -wallet /u01/app/oracle/admin/11gr2/wallet -pwd WalletPasswd123 -auto_login   

The password should follow the password policy, else you might get an error saying wallet creation failed

Now import the root and intermediate certificate that we have copied

 $ orapki wallet add -wallet /u01/app/oracle/admin/11gr2/wallet -trusted_cert -cert "/location_of_the_cert/root.cer" -pwd WalletPasswd123  
 $ orapki wallet add -wallet /u01/app/oracle/admin/11gr2/wallet -trusted_cert -cert "/location_of_the_cert/intermediate.cer" -pwd WalletPasswd123  

Step 4:

execute the SQL :

 EXEC utl_http.set_wallet ( 'file:/u01/app/oracle/admin/11gr2/wallet', 'WalletPasswd123');  
 EXEC show_html_from_url(https://www.google.com)  
 ..............  
 ..............  
 PL/SQL procedure successfully completed  
 SQL>