Database Connection through Wallet Authorization

1. How to Create Wallet ?

From Oracle Database end, you can create/manage certificates using tools such as Oracle Wallet Manager, orapki utility.

Documented under 'Oracle Advanced Security' 

Oracle Database Advanced Security Administrator's Guide

11g Release 2 (11.2)

Part Number E10746-02

Chapter 12 Configuring Secure Sockets Layer Authentication

URL: http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asossl.htm#i1022705 

After creating wallets you can use establish connection using wallet authorization through utl_smtp.open_connection.

2. Oracle connection can be established using wallet authorization:  Samples

Example 1:

DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
     host => 'smtp.example.com',
     port => 25,
     wallet_path => 'file:/oracle/wallets/smtp_wallet',
     wallet_password => 'password',
     secure_connection_before_smtp => FALSE);
  utl_smtp.starttls(c);
END;

Example 2:

DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection(
      host => 'smtp.example.com',
      port => 25,
      wallet_path => 'file:/oracle/wallets/smtp_wallet',
      wallet_password => 'password',
      secure_connection_before_smtp => FALSE);
  UTL_SMTP.STARTTLS(c);
  UTL_SMTP.AUTH(
      c => c,
      username => 'scott',
      password => 'password'
      schemes  => utl_smtp.all_schemes);
END;

Reference / Read More:

Oracle Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
E40758-03

Chapter 236 UTL_SMTP
URL: http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/u_smtp.htm

3. Troubleshooting Connections: 

You should be able to perform: 

%> ping -a <hostname>

Example:

%> ping -a www.google.com

If that results in a successful ping, then UTL_HTTP will work the connecting from Oracle Database Server:

    

Example: 

SQL> SELECT utl_http.request('http://www.google.com') FROM dual;

And if that works; 

You would then need to check on the Wallet connection: 

Example:

[sunsys]/etc/ORACLE/WALLETS/oracle> ls -al

drwxr-xr-x   2 oracle   dba          512 Jan 28 11:33 ./

drwxr-xr-x  10 oracle   dba          512 Jan 30 08:39 ../

-r--------   1 oracle   dba         8581 Jan 17 11:31 ewallet.p12

With the wallet configured, access to the external web site can be tested using the following SQL.

SELECT utl_http.request('<url or secure url>', '<proxy server:port>', 'file:<wallet reference>', '<wallet password>') FROM DUAL; 

For example:

SELECT utl_http.request('http://www.google.com','proxy.mine.yours.com:80',

                        'file:/etc/ORACLE/WALLETS/oracle','welcome1') FROM DUAL;