Installing Oracle SQL*Plus client on Ubuntu

Case type:

Service area: 

Technologies:

Problem

You need to connect with sqlplus command line tool to your Oracle database but there are no binaries packages available for Debian/Ubuntu.

Solution

Convert and install from Oracle Instantclient RPM files. If case, tune your installation to correctlly access the Oracle client libraries.

Download

You'll need at least three RPM files. Go to http://www.oracle.com/technetwork/database/features/instant-client/index... and, once you have accepted the "License Agreement", download "Basic", "SQL*Plus" and the "SDK" RPMs.

In my case (Ubuntu 14.04 LTS, Intel on 64-bit) my downloaded files were:

  • oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
  • oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
  • oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

Convert and Install

In order to convert from .rpm to .deb, you'll need alien:

$ sudo apt-get install alien

Now convert and install the packages in this order:

$ sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
$ sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
$ sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Test & Fix

Test your Oracle client. User either sqlplus either sqlplus64 depending on your platform:

$ sqlplus64 username/password@//dbhost:1521/SID

If sqlplus yields of a missing libaio.so.1 file, run:

$ sudo apt-get install libaio1

If you get the next message, then you need to instruct sqlplus to use the proper libray:

sqlplus64: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

To do so, first find the location of Oracle libraries. The path should be something like /usr/lib/oracle/<version>/client(64)/lib/. In my case (Ubuntu 14.04 LTS, Intel on 64-bit), it was /usr/lib/oracle/12.1/client64/lib/.

Now, add this path to the system library list. Create and edit a new file:

$ sudo nano /etc/ld.so.conf.d/oracle.conf

Add inside the path:

/usr/lib/oracle/12.1/client64/lib/

Run now the dynamic linker run-time bindings utility:

$ sudo ldconfig

For other errors when trying to run sqlplus, please consult the Ubuntu help page.

Wait! History? Auto-completion?

The weird thing about SQL*Plus is that it doesn't provide history and auto-completion like the MySQL client does. Here comes rlwrap, the "readline wrapper" — a small utility that uses the GNU readline library to allow the editing of keyboard input for any command.

Installing rlwrap on Ubuntu is simple:

$ sudo apt-get install rlwrap

Now you can run SQL*PLus wrapped with rlwrap:

$ rlwrap sqlplus64 username/password@//dbhost:1521/SID

Now you have line commands history in sqlplus. Nice :)

But what about auto-completion? Well, that needs some more workingaround and is not in the scope of this post. But you can check this post and figure a way to add tab completion to SQL*Plus.

It's time to make you life easy by adding an alias to .bashrc. Just ad this line:

alias sp='rlwrap sqlplus64 username/password@//dbhost:1521/SID'

and now you can simply use:

$ sp

to connect to your database.

Enjoy!

Resources