Introduction

Accessing Oracle databases from 'C' client programs has been a difficult task. It required installing the bloated Oracle client software, and then dealing with the OCI libraries that where diffcult to work with. This how-to describes the setup of a lean Oracle client using the Oracle InstantClient libraries together with libsqlora8 as a interface for easy database access from 'C' programs.

Get the prerequisite software packages:

instantclient: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
instantclient-basic-linux32-10.2.0.2-20060331.zip
instantclient-sdk-linux32-10.2.0.2-20060331.zip
libsqlora8: http://www.poitschke.de/libsqlora8/
libsqlora8-2.3.3.tar.gz

Install the instantclient

Create the oracle home directory

fm@susie:~ # mkdir /home/oracle
fm@susie:~ # cd /home/oracle

Extract the instantclient libraries

fm@susie:/home/oracle # unzip /mnt/hgfs/instantclient-basic-linux32-10.2.0.2-20060331.zip
Archive:  /mnt/hgfs/instantclient-basic-linux32-10.2.0.2-20060331.zip
  inflating: instantclient_10_2/classes12.jar
  inflating: instantclient_10_2/libclntsh.so.10.1
  inflating: instantclient_10_2/libnnz10.so
  inflating: instantclient_10_2/libocci.so.10.1
  inflating: instantclient_10_2/libociei.so
  inflating: instantclient_10_2/libocijdbc10.so
  inflating: instantclient_10_2/ojdbc14.jar

Create a symlink lib32 to point to the instantclient directory

fm@susie:/home/oracle # ln -s instantclient_10_2 lib32

Extract the instantclient sdk containing the header files

fm@susie:/home/oracle # unzip /mnt/hgfs/instantclient-sdk-linux32-10.2.0.2-20060331.zip
Archive:  /mnt/hgfs/instantclient-sdk-linux32-10.2.0.2-20060331.zip
   creating: instantclient_10_2/sdk/
   creating: instantclient_10_2/sdk/include/
  inflating: instantclient_10_2/sdk/include/occi.h
...
  inflating: instantclient_10_2/sdk/include/nzerror.h
   creating: instantclient_10_2/sdk/demo/
...
  inflating: instantclient_10_2/sdk/demo/occiobj.typ
 extracting: instantclient_10_2/sdk/ottclasses.zip
  inflating: instantclient_10_2/sdk/ott

create a symlink of the sdk include directory to the oracle home

fm@susie:/home/oracle # ln -s instantclient_10_2/sdk/include include

fm@susie:/home/oracle/instantclient_10_2 # ls
.   classes12.jar      libnnz10.so      libociei.so      ojdbc14.jar
..  libclntsh.so.10.1  libocci.so.10.1  libocijdbc10.so  sdk

Create generic symlinks for the versioned Oracle libraries

fm@susie:/home/oracle # cd instantclient_10_2/
susie:/home/oracle/instantclient_10_2 # ln -s libclntsh.so.10.1 libclntsh.so
susie:/home/oracle/instantclient_10_2 # ln -s libocci.so.10.1 libocci.so

Add the instantclient libraries to "/etc/ld.so.conf" by adding a line like /home/oracle/lib32 (choose the real lib location), run ldconfig and check the libraries are found:

fm@susie:~ # ldconfig -p | grep oci
        libocijdbc10.so (libc6) => /home/oracle/lib32/libocijdbc10.so
        libociei.so (libc6) => /home/oracle/lib32/libociei.so

Install libsqlora8

Extract libsqlora8 

fm@susie:/home/devel # zcat /mnt/hgfs/libsqlora8-2.3.3.tar.gz | tar xf -

Configure libsqlora: Setting the ORACLE_CPPFLAGS environment variable is important because libsqlora8 expects the standard include location of a full Oracle install.

fm@susie:/home/devel # cd libsqlora8-2.3.3
fm@susie:/home/devel/libsqlora8-2.3.3 # export ORACLE_CPPFLAGS="-I/home/oracle/include"
fm@susie:/home/devel/libsqlora8-2.3.3 # ./configure --prefix=/home/oracle --with-oraclehome=/home/oracle  --with-oraversion=10.2
checking build system type... i686-suse-linux
checking host system type... i686-suse-linux
...
config.status: creating config.h
config.status: executing depfiles commands
config.status: executing default commands

Time for "make" and "make install". Since "make install" does not copy the libsqlora-config.h to /home/oracle/include although it is included in sqlora.h line 65. We correct it by hand.


fm@susie:/home/devel/libsqlora8-2.3.3 # make
fm@susie:/home/devel/libsqlora8-2.3.3 # make install
fm@susie:/home/devel/libsqlora8-2.3.3 # cp libsqlora8-config.h /home/oracle/include

Add libsqlora to /etc/ld.so.conf

susie:/home/devel/libsqlora8-2.3.3 # vi /etc/ld.so.conf
/home/oracle/lib
susie:/home/devel/libsqlora8-2.3.3 # ldconfig -p | grep libsql
        libsqlora8-2.3.so.0 (libc6) => /home/oracle/lib/libsqlora8-2.3.so.0

Time to try:

susie:/home/devel/libsqlora8-2.3.3 # examples/examples
Cannot login with scott/tiger

Which database should we connect to? Here we set up the DB connectivity:

susie:~ # mkdir /home/oracle/network
susie:~ # mkdir /home/oracle/network/admin
susie:~ # echo "NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)" > /home/oracle/network/admin/sqlnet.ora
susie:~ # vi /home/oracle/network/admin/tnsnames.ora

WORK1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ori)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = work1.frank4dd.com)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Now the test should work (if the db exists and scott/tiger can login to the example tables.

susie:/home/devel/libsqlora8-2.3.3> # cd examples
susie:/home/devel/libsqlora8-2.3.3/examples # ./examples scott/tiger@work1
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Reduced the salary of 3 managers
Employees with SAL > 1000.00 :
ENAME SAL
-----+---+
ALLEN 1600
WARD  1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING  5000
TURNER 1500
ADAMS 1100
FORD  3000
MILLER 1300
Doubled the salary of 3 managers.
Employees with salary >= 3000:
Name=FORD     Salary=3000.00
Name=SCOTT    Salary=3000.00
Name=KING     Salary=5000.00
Employees with salary >= 1200:
Name=MARTIN   Salary= 1250.00
Name=WARD     Salary= 1250.00
Name=MILLER   Salary= 1300.00
Name=TURNER   Salary= 1500.00
Name=ALLEN    Salary= 1600.00
Name=CLARK    Salary= 2450.00
Name=BLAKE    Salary= 2850.00
Name=JONES    Salary= 2975.00
Name=FORD     Salary= 3000.00
Name=SCOTT    Salary= 3000.00
Name=KING     Salary= 5000.00
Selected 11 employees
Insert CLOB
Update CLOB
Query CLOB
Compare CLOB
Employees with salary (via refcursor) >= 1200:
Name=MARTIN   Salary= 1250
Name=WARD     Salary= 1250
Name=MILLER   Salary= 1300
Name=TURNER   Salary= 1500
Name=ALLEN    Salary= 1600
Name=CLARK    Salary= 2450
Name=BLAKE    Salary= 2850
Name=JONES    Salary= 2975
Name=FORD     Salary= 3000
Name=SCOTT    Salary= 3000
Name=KING     Salary= 5000
Employees with salary (via refcursor with bind vars) >= 1200:
Name=MARTIN   Salary= 1250.00
Name=WARD     Salary= 1250.00
Name=MILLER   Salary= 1300.00
Name=TURNER   Salary= 1500.00
Name=ALLEN    Salary= 1600.00
Name=CLARK    Salary= 2450.00
Name=BLAKE    Salary= 2850.00
Name=JONES    Salary= 2975.00
Name=FORD     Salary= 3000.00
Name=SCOTT    Salary= 3000.00
Name=KING     Salary= 5000.00
Employees, departements and locations via ntable
ENAME=       KING
    DNAME=     ACCOUNTING LOC=       NEW YORK
    DNAME=       RESEARCH LOC=         DALLAS
    DNAME=          SALES LOC=        CHICAGO
    DNAME=     OPERATIONS LOC=         BOSTON
ENAME=     MILLER
    DNAME=     ACCOUNTING LOC=       NEW YORK
    DNAME=       RESEARCH LOC=         DALLAS
    DNAME=          SALES LOC=        CHICAGO
    DNAME=     OPERATIONS LOC=         BOSTON
Example20:
Employees with SAL > 1000.00 :
ENAME     SAL
---------+-------+
ALLEN     1600
WARD      1250
JONES     2975
MARTIN     1250
BLAKE     2850
CLARK     2450
SCOTT     3000
KING      5000
TURNER     1500
ADAMS     1100
FORD      3000
MILLER     1300
susie:/home/devel/libsqlora8-2.3.3/examples #

Success! Another test:

susie:/home/devel/libsqlora8-2.3.3/examples # ./examples dbadm/xxx@it2.f30db
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Table EMP does not exist. Please install Oracle demo tables

See also: