Ilmar Kerm

Oracle, databases, Linux and maybe more

This post is continuing my previous port about modifying SQLDeveloper preferences with ansible. Building on the same motivation and technique my goal in thist post is to centrally push out and keep updated connection details for SQLDeveloper on client side.

First lets declare the connections we want to push out:

NB! I’m pushing out connections referring to TNS names, since I want to add some extra RAC related settings to each connection description.

First need to create connection.yml that will contain tasks to add a single connection to SQL Developer. This file will be called for every connection from the main playbook.

Now the main playbook.

NB! This is just an extract from the playbook. I expect you are familiar with ansible and know how to put all these three files together 🙂

Some background info: We need to provide direct database access to many developers and data analysts. On database side managing so many different users securely means you have to use the same authentication source as all the other systems in the company, for example Active Directory. For databases we have implemented using Radius and for Oracle database this means users have to use thick Oracle client on their computers.
Installing Oracle client is not a trivial task for many users and the MacOS instantclient that Oracle provides has been very dependent on the exact MacOS version. In order to provide some help to overcome these problems I decided to create a small Virtualbox VM, that users could just download and run. The VM would be based on Fedora Linux, have preconfigured Oracle Instantclient with common client tools like SQLPlus and SQL Developer.
This kind of VM needs to be maintained and to push out changes I already set up a git respository containing ansible playbook. In VM, under root user I checked out this git respository and set up regular cron job to run one specified playbook locally.
Hourly crontab job looks something like this:

cd ansible-sqldeveloper-vm && git pull && ansible-playbook local.yml

This playbook can be used to push out changes to application level also, for example maintaining common tnsnames.ora file to include newly created databases.

It did not take long to discover first bug with the setup – SQL Developer under Linux “looses” text cursor in editor window very easily, it is just hidden. To change that users need to go to SQL Developer preferences and change how text cursor looks like. I don’t want users to fix this common error themselves.
Another issue, new SQL Developer version came out. It is easy to push out new RPM using Ansible but SQL Developer does not start immediately after that, users have to configure there to find JDK first. Another thing I really don’t want my users to deal with.

Ansible to the rescue. All I needed to do was commit the necessary changes to my local.yml playbook and update it in the git repository.

Lets take the easier issue of telling newly installed SQL Developr where to find JDK first.
SQL Developer 17.4 rads the JDK location from file $HOME/.sqldeveloper/17.4.0/product.conf so /home/sqldeveloper/.sqldeveloper/17.4.0/product.conf in my case. This file contains only one important line telling the location of JDK:

SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk

All I need to do is use ansible copy module to create this file.

Now the other problem, I need to change specific SQL Developer preferences. When I did the changes required on a test system I found out that SQL Developer wrote it preferences to XML file $HOME/.sqldeveloper/system17.4.0.355.2349/o.sqldeveloper/product-preferences.xml. The file location is SQL Developer version specific. The specific change was a newly added XML tag under root element:

   <hash n="CaretOptions">
      <value n="caretColor" v="-31744"/>
      <value n="insertShape" v="8"/>
      <value n="overwriteShape" v="5"/>

This file contains all user preferences, so I don’t really want to overwrite this entire file, I only want to add this new XML tag. This is possible using Ansible xml module where I can give XPath as an argument and the module will make sure that the provided XPath expression is present in the file.
Also I want this to work for all SQL Developer versions and not change the path every time SQL Developer is upgraded.

The resulting Ansible playbook looks like this.

First I’ll describe the needed changes in a separate Ansible parameters file just to make the playbook look cleaner.

The playbook itself: