Connecting to a database using SQLcl, a wallet and the thin driver

I have previously written about the use of SQLcl and using an Oracle wallet (aka secure external password store). In my previous article I used the JDBC oci driver. This is perfectly fine, but as it’s based on Oracle’s Call Interface, it requires client libraries to be present. This time I wanted to use the thin driver. A cursory search didn’t reveal any recent information about the topic so I decided to write this short post.

Creating the Wallet

The wallet is created exactly the same way as described in an earlier article of mine. Scroll down to “Preparing the External Password Store” and have a look at the steps required. You probably don’t have a client installation on the sqlcl host, so you need to prepare the wallet on a machine with one present. When providing input to mkstore and when creating tnsnames.ora it still seems to be best to ensure all identifiers are in lower case. Even if you db_name is in upper case like mine (“ORCL”).

Once the wallet is created and tested, transfer it to the sqlcl-host in a secure manner.

Deploying SQLcl

SQLcl – among other things – allows you to connect to an Oracle database without a client installation. This is quite useful in many cases. I downloaded SQLcl 21c (build 21.1.0.104.1544) from Oracle’s website and deployed it in ~/sql.

Testing the Wallet

I dropped the wallet and associated configuration files created earlier into ~/tns on my sqlcl-host. There is no need to set any environment variables at all. For reference, the following files were present on this vagrant box:

[vagrant@ords ~]$ ls -l ~/tns
total 20
-rw-------. 1 vagrant vagrant 581 Apr 20 19:29 cwallet.sso
-rw-------. 1 vagrant vagrant   0 Apr 20 19:28 cwallet.sso.lck
-rw-------. 1 vagrant vagrant 536 Apr 20 19:29 ewallet.p12
-rw-------. 1 vagrant vagrant   0 Apr 20 19:28 ewallet.p12.lck
-rw-r--r--. 1 vagrant vagrant  89 Apr 20 19:30 ojdbc.properties
-rw-r--r--. 1 vagrant vagrant 120 Apr 20 19:30 tnsnames.ora
[vagrant@ords ~]$  

With the files in place you connect to the database as follows:

[vagrant@ords bin]$ ./sql /@jdbc:oracle:thin:/@orcl?TNS_ADMIN=/home/vagrant/tns

SQLcl: Release 21.1 Production on Tue Apr 20 20:17:12 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 20 2021 20:17:15 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> select host_name from v$instance;

HOST_NAME 
-------------
server3      

SQL>   

Note the leading forward slashes in the connection string, they are absolutely mandatory. The beauty of EZConnect Plus syntax is that I don’t need to specify TNS_ADMIN as an environment variable anymore. EZConnect Plus can do many more things, feel free to browse the white paper I linked to.

Have fun!