Fetch data via REST API over HTTPS using Oracle Wallet

Hello there!

This note explains how to obtain and manage security credentials for Oracle Application Server resources and accessing outside database. Security administrators can use Oracle Wallet Manager and its command-line utility using orapki to manage public key infrastructure (PKI) credentials on Oracle clients and servers. These tools create credentials that can be read by Oracle Database.

Oracle HTTPS supports HTTP 1.0 and HTTP 1.1 connections between a client and a server.

In this case, I need to fetch data via a REST API over HTTPS from outside the database. When the package fetches data from a web site using HTTPS, it requires Oracle Wallet Manager which can be created by either Oracle Wallet Manager or the orapki utility. Non-HTTPS fetches do not require an Oracle wallet.

1. CHECK AN EXISTING WALLET PATH IN CURRENT SERVER

First thing first, you need to view existing wallet path by run the query statement, like below:

SQL> SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER
--------------------------------------------------------------------------------
/u01/app/oracle/admin/oracle/wallet

As you can see, above result saying the wallet path is on /u01/app/oracle/admin/oracle/wallet

2. CREATE WALLET FOLDER

Therefore, please verify the folder as mentioned earlier. If the folder does not exist, we will need to create a folder at the specified path further.

Command:

$ orapki wallet create -wallet <your_wallet_path> -pwd <your_password> -auto_login

Here is the complete command:

$ cd /u01/app/oracle/admin/oracle
$ mkdir wallet
$ orapki wallet create -wallet /u01/app/oracle/admin/oracle/wallet -pwd your_password -auto_login

Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

3. ADD A CERTIFICATE TO THE ORACLE WALLET PATH

Command :

$ orapki wallet add -wallet <your_wallet_path> -trusted_cert -cert "<your_crt_file>" -pwd <your_password>

Here is the complete command:

$ orapki wallet add -wallet /u01/app/oracle/admin/oracle/wallet -trusted_cert -cert "/home/oracle/cert/www.google.com.crt" -pwd your_password
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

^{Note: Before you add some certificate in your Oracle Wallet, you need to retrieve *.crt file from the target website as well.}

4. DISPLAYING A WALLET

Command:

$ orapki wallet display -wallet <your_wallet_path> -pwd <your_password> -complete

Here is the complete command:

$ orapki wallet display -wallet /u01/app/oracle/admin/oracle/wallet -pwd your_password -complete
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=dummyjson.com
Issuer:         CN=Zscaler Intermediate Root CA (zscalertwo.net) (t)\20,OU=Zscaler Inc.,O=Zscaler Inc.,ST=California,C=US
Serial Number:  7F:59:7B:7D:C6:52:1C:3E:05:11:44:68:D1:AD:E3:8C:53:37
Key Length      2048
MD5 digest:     22:FA:07:6C:70:72:1F:6F:DE:09:AC:2E:E3:CA:D3:4D
SHA digest:     29:BF:5F:7C:48:40:27:34:E6:1C:C5:E0:C1:7D:AF:55:EB:11:2C:22

Subject:        CN=www.google.com
Issuer:         CN=Zscaler Intermediate Root CA (zscalertwo.net) (t)\20,OU=Zscaler Inc.,O=Zscaler Inc.,ST=California,C=US
Serial Number:  76:53:5E:ED:49:90:12:C1:0A:63:E9:5E:DC:4C:D1:36
Key Length      2048
MD5 digest:     18:81:17:AD:E4:AC:13:55:17:AA:53:D3:5D:A4:0D:91
SHA digest:     0F:1F:A2:B5:87:D6:B4:73:F1:A5:20:CD:60:23:6C:2D:53:EA:06:F0

On above the wallets information, you’d noticed 2 trusted certificates displayed. dummyjson.com and www.google.com, you can add another certificate by doing step #3. This indicates that these websites can be accessed via HTTPS since they have been added to the Oracle Wallet.

5. DELETING A WALLET

Just to make sure, if you are on testing and debugging. You also need to know how to delete your existing wallet. Here is how to delete it.

Command:

$ orapki wallet remove -wallet <your_wallet_path> -trusted_cert_all -pwd <your_password>

Here is the complete command:

$ orapki wallet remove -wallet /u01/app/oracle/admin/oracle/wallet -trusted_cert_all -pwd your_password

6. RUN A QUERY

Run a query containing utl_http which a package that makes HTTP(S) callouts from SQL and PL/SQL. url_http requires an APEX installed. According the documentation stated, the parameter should be like below.

SELECT UTL_HTTP.REQUEST('<URL>', '<proxy_hostname>:<proxy_port_number>', '<your_wallet_path>', '<wallet_password>') FROM DUAL;

So, here is the result.

SQL> SELECT UTL_HTTP.REQUEST('https://www.google.com',  null,  'file:/u01/app/oracle/admin/oracle/wallet', 'your_password') FROM DUAL;               

UTL_HTTP.REQUEST('HTTPS://WWW.GOOGLE.COM',NULL,'FILE:/U01/APP/ORACLE/ADMIN/ORACL
--------------------------------------------------------------------------------
<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage" lang="en-
SG"><head><meta content="text/html; charset=UTF-8" http-equiv="Content-Type"><me
ta content="/images/branding/googleg/1x/googleg_standard_color_128dp.png" itempr
op="image"><title>Google</title><script nonce="yGGrChF9ihlZp_NfNPF0pA">(function
(){var _g={kEI:'bkuDZuiuKJzf4-EPge2yqAY',kEXPI:'0,793108,2907191,650,432,6,53865
8,2872,2891,11754,31274,46127,18161,145698,2,16737,23024,6700,41948,57734,2,2,1,
10956,12871,10960,23350,22436,9779,62657,73179,3030,15816,1804,7734,13278,5396,8
860,11814,477,1158,9708,13154,33497,5211784,742,150,622,38,5991770,2839759,16,52
7,239,6,18,4,51,1,47,2,5,27981460,16672,43887,3,318,4,1281,3,2124363,23029351,79
50,1,212,4636,8409,3321,4706,8637,22906,5121,36870,10511,2370,6407,13846,10473,2
479,2212,8181,45290,4140,10085,11590,2482,2313,1954,155,2484,13504,7736,6599,442

UTL_HTTP.REQUEST('HTTPS://WWW.GOOGLE.COM',NULL,'FILE:/U01/APP/ORACLE/ADMIN/ORACL
--------------------------------------------------------------------------------
,2098,967,540,1642,1450,207,121,3217,4,3004,6760,7137,1694,4082,409,519,4408,3,8
8,359,5,1009,1517,1644,48,3941,687,2455,2,2771,1401,48,1119,3,418,1353,154,425,3
360,1543,2902,34,971,709,2745,2,2,1870,1704,333,3531,682,659,19,3113,2544,34,476
,1662,319,1037,15,1535,324,991,498,1,766,373,1490,437,286,2,9,623,1457,273,6,136
7,4,267,3,836,378,58,668,244,686,1424,4,20,4,245,294,834,663,508,1201,38,238,3,1
94,868,125,1,6,262,271,141,1575,367,3,93,1030,652,24,108,178,3,1,3,34,273,222,66
,448,51,23,1,3,1,1103,162,2,1070,1099,534,26,140,12,438,1581,95,554,1283,1033,12
,245,4,222,181,504,624,213,709,563,130,418,114,252,1434,492,1358,334,57,461,11,8
9,180,988,152,1202,39,718,212,22,296,33,1050,72,309,909,800,11,78,359,124,89,2,1
15,177,964,1141,91,1206,674,133,282,80,219,202,347,806,23,416,342,26,1177,2411,2
1182823,365637,3,980,5,17241,113,429,1777,20,390,175',kBL:'dsEj',kOPI:89978449};

UTL_HTTP.REQUEST('HTTPS://WWW.GOOGLE.COM',NULL,'FILE:/U01/APP/ORACLE/ADMIN/ORACL
--------------------------------------------------------------------------------
(function(){var a;((a=window.google)==null?0:a.stvsc)?google.kEI=_g.kEI:window.g
oogle=_g;}).call(this);})();(function(){google.sn='webhp';google.kHL='en-SG';})(
);(function(){
var h=this||self;function l(){return window.google!==void 0&&wind

Voila! You can now see the HTML content of its website, confirming that your script is running perfectly.

That’s all folks! Happy coding! 🥰😍

Sandy's simple note

My Insight