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.

On Monday August 31^st, Gowlings hosted a debate on open source licenses organized by the Free and Open Source Software Learning Centre (FOSSLC).

The debate was conducted between the proponents of three major Open Source licenses: Mike Milinkovich for the EPL, Matt Asay for the GPL, and David Maxwell for the BSD license.

It was organized into three rounds: first the panelists had ten minutes to sell us their license of choice. Then they were given five minutes to rebut points made by the two other panelists. A final one minute was given to rebut any rebuttal. After those three rounds, the audience—both the live one and that watching the feed—asked their questions.

From what I could estimate, between 50 and 70 people physically attended the event. Andrew reported that between 25 and 50 viewed the live feed. Videos of the event are available on the FOSSLC site.

Read the rest of this entry . . .

The latest release of DBD::Oracle is now ready and can be found at: CPAN DBD::Oracle. It is a Perl module that works with the DBI module to provide access to Oracle databases. It is maintained by me, John Scoles, under the auspices of The Pythian Group as open source/free software.

This release is largely a maintenance release that fixes a number of bugs (new and old) and cleans up the POD. I have also spent a good deal of time cleaning up the tests, compile warnings, and the Makefile.PL. A thank-you goes out to Martin Evans and H. Merijn Brand for their help on that.

As for new items, the ORA_VERBOSE attribute was added so that you can debug/trace DBD::Oracle without seeing any DBI debug/trace output. OCILobGetLength was added to the LOB functions, thanks to Milo van der Leij, I have also dropped support for Oracle 8 and earlier clients; see the POD for details on this.

Finally, I have also cleaned up and expanded how persistent LOBs work. A thank-you to Eric Simon for his work on this.

The release has been fully tested with version (1.601) of DBI. Below is the list of the changes and/or fixes in this release.

Read the rest of this entry . . .

The latest release of DBD::Oracle is now ready and can be found at: CPAN DBD::Oracle. It is a Perl module that works with the DBI module to provide access to Oracle databases. It is maintained by me, John Scoles, under the auspices of The Pythian Group as open source/free software.

The release has been fully tested with the latest version of DBI (1.601).

Below is the list of the changes and/or fixes in this release.

Changes in DBD-Oracle 1.21(svn rev 11067) 11th April 2008

• Added Notes to README.win32.txt on installing Instant Client 1.1.0.6.0; from John Scoles
• Added the oci_typecode_name method to get the name rather than just the number of an OCI_TYPECODE; from John Scoles
• Fixed a unreported bug with Embedded Objects; from John Scoles
• Fixes for #34621 & 33791 from RT cpan
• Added patch to allow faster fetch from REF CURSORs; from Biswadeep Chowdhury
• Updated the Todo file for next version; from John Scoles
• Added support for the 10.2 Data Interface for Persistent LOBs; by John Scoles
• Changed the way pre-fetching is done; by John Scoles
• Added support for Scrollable cursors; from John Scoles
• Changed the max size of cache_rows to a sb4 rather than a int and or a ub4; from John Scoles
• Added support for Lobs in select of OCI Embedded Objects; from John Scoles with a big thankyou to Paul Weiss
• Fixed for embedded object in object; from Paul Weiss
• Added support for direct insert of large XML data into XMLType fields; from Hendrik Fuss & John Scoles
• Fixed memory leak (not releasing Temp Lob with OCILobFreeTemporary) when created for a bind; from John Scoles
• Added support for bind_param_inout_array for use with execute_array; from John Scoles
• Added enhancement for Embedded Objects handling; from Paul G. Weiss
• Fixed to Makefile.PL let it read makefiles from other makes from; Alexander V Alekseev
• Updated POD to tell users to Avoid Using “SQL Call”; from Charles Jardine
• Updated POD to account for rt.cpan.org #30910: “DBD-Oracle crashes when trying to read empty LOB”; from John Scoles
• Added DBD::Oracle impdata/threads patch; from Jeffrey Klein

In this release there are a number of new features. One important one you might want to have a look at is support for binding an array for in-out operations with execute_array. Given this SQL:

INSERT INTO foo (id, bar) VALUES (foo_id_seq.nextval, ?) RETURNING id INTO ?

and using execute_array to insert 200 records, you will get an array back of the 200 new IDs inserted.

I have also added support for directly inserting of large XML files into an XMLType field.

Another important addition is support for Persistent LOBs if you are using Oracle 10.2 or later. You can now treat LOBs as if they were any other form of data, so say goodbye to LOB locators.

Finally, I have added support for native Oracle ‘Scrolling Cursors’.

There were also a number of bug fixes and code updates as listed above.

Cheers, John Scoles.