Pythian Group

Among the features announced with the release of version 4.6 of the KDE Software Compilation is KateSQL, a SQL Query plugin for the Kate text editor providing the basic functionality of a SQL client. It leverages the Qt SQL module, allowing you to make a connection to most types of databases. Out of the box it includes support for MySQL and SQLite, and with this post I’ll show you how to get it to connect to Oracle databases as well.

Since I am an Ubuntu user (Note: you’ll need to be on 11.04 and either be running Kubuntu or have the ‘kate’ package installed) these instructions will be specific to that operating system, though they will likely also apply to other Debian-based distributions with minimal modification. However, if you are using an RPM-based distribution such as openSUSE or Fedora, you’ll need to adapt the instructions somewhat to make sure you have the necessary development packages installed.

The first thing we’ll need to do in order to get this to work is to obtain an Oracle client. This can be downloaded from the Oracle Technology Network and requires that you have a valid Oracle.com Single Sign-On account. For the purposes of this article we’re going to use the Linux x86 Oracle Instant Client and the files we’re going to download are:

oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm


The first package is the client itself, providing the files required to run OCI applications. The second is the Instant Client SDK which includes header files for developing Oracle applications with Instant Client: in our case an OCI library for the Qt SQL module.

Users of RPM-based distributions can just go ahead and install these packages while others will need to convert them to .deb format first. The best way to do this that I am aware of is with the tool called Alien. Let’s install it, convert the Oracle Instant Client packages, and install them too.

cd /path/to/oracle/instant/client/files
sudo apt-get install alien
sudo alien oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
sudo alien oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm
sudo dpkg -i oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.deb oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.deb

The next step is to install all of the dependencies necessary to build the Qt SQL OCI library.

sudo apt-get build-dep qt4-x11
sudo apt-get install qt4-qmake libqt4-dev libaio1

Then we will need the actual source code for the qt4-x11 package. This can be obtained with the following command:

apt-get source qt4-x11

To recap, at this point we’ve downloaded and installed the Oracle Instant Client and all of the dependencies required to build the qt4-x11 package, and we’ve downloaded the qt4-x11 source code. Next we’re going to build the Qt SQL OCI library itself.

sudo ln -s /path/to/qt4-x11-4.7.0/src /usr/include/src
sudo cp -r /path/to/qt4-x11-4.7.0/include/QtSql/private /usr/include/qt4/QtSql/
cd /usr/include/src/plugins/sqldrivers/oci/
qmake "INCLUDEPATH+=/usr/include/oracle/11.2/client64" "LIBS+=-L/usr/lib/oracle/11.2/client64/lib -Wl,-rpath,/usr/lib/oracle/11.2/client64/lib -lclntsh -lnnz11" oci.pro
make
sudo make install

Now that the libqsqloci.so module has been built and installed, Kate should be able to connect to an Oracle database. Launch Kate (or close and re-open it if it was already running), click Settings -> Configure Kate, expand the Application tree if necessary, click Plugins, check ‘SQL Plugin‘ and click OK. Then click SQL -> Add connection to launch the connection wizard. In addition to the MySQL and SQLite database drivers, you should now also have drivers named QOCI and QOCI8 available. Choose the appropriate one, enter relevant connection details, and complete the connection wizard. You can now execute queries against the database and display result data.

I have recently stumbled upon V$SESSION_CONNECT_INFO view and discovered that it provides interesting information about client-side software and settings.

Using this view in Oracle 11g you can simplify collecting some statistics about database clients. Here is what can be extracted:

• Version of client libraries
• Type of OCI library used (standard OCI, different instant clients and etc.) but no JDBC support it seems
• Client characterset (new in 11g – doesn’t work for pre-11.1 clients and for JDBC thin)
• Authentication type (username+password, OS based, proxy and etc.)

How many times have you been in the situation when you need to know certain attributes of your clients to evaluate impact of a coming change of a bug you just hit? Unless the environment is very simplistic or just recently setup, there is usually no easy way to discover every client driver used to connect. This view can workaround absence of standard policies and documentation, procedure violations and you can confidently determine which client versions are where. You can even setup monitoring and pro-actively generate an alert when violations are detected which would be my preferred way.

If you decide to use this feature, you might want to create an AFTER LOGON trigger – sampling of could be not enough to catch short-living sessions.

I haven’t used this view myself and I’m writing this on the plane as I’ve just come across it in the documentation and though that this rather unknown feature would be useful on the blog.

It’d be nice to have that info available in audit views but I couldn’t find it in DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL. DBA_AUDIT_TRAIL has just column COMMENT_TEXT where we can see authentication type for session records.

Oh… which plane am I on? That deserves a separate blog post. Stay tuned!

PS: Oh… I’ve seen a ghost of Dave Ensor now on the plane… It’s probably not such a good idea to read Oracle manuals on the plane – it was just a man with very similar face profile. Yeah, I better switch to a movie then!

PPS: Actually, it took me few days to post this entry. Shame on me but better sooner then later… Oh… I mean better later than never!