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/technetwork/database/features/instant-client/index-097480.html
At the time of writing, I got:
- 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
Database Connection
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)
)
)
Database Tests
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
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