Connect Oracle database on Eclipse Linux using OCCI

To develop database application that use Oracle. Of course with Oracle database installed, programer must prepaire two packages provided by Oracle. One use to provide share library (*.so files) for application to run under Linux, another one is provide class header (*.h files) that programer can include in their application. We could download that two packages at http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html, it's called Instant Client.

I'm using CentOS 6.2 64 bit, so I will download Instant Client for Linux x86-64, there any many packages, however, only two package we need:

  1. Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications.
  2. *Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support.
  3. *Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client.

The first and second is the same, Basic Lite is smaller than Basic with fewer language support. Everyone need to choose between one of them. In my opinion, I install the first and the last packages.

  • Instant Client Package - Basic for Linux 64 bit provide share library at /usr/lib/oracle/11.2/client64/lib, this is required libraries to run OCCI program.
  • Instance Client Package - SDK for Linux 64 bit provide additional header files at /usr/include/oracle/11.2/client64, this is c++ header files that we need to include in our program.

Next step, we must tell Eclipse the path to the share libraries and the header files by: on Eclipse top menu, go to Project/Properties/C++ General/Path and Symbols

Add Oracle OCCI  header files and libraries into eclipse
Eclipse Path and Symbols properties.

To-do list:

  • Add header files directory into Include tab.
    (Add /usr/include/oracle/11.2/client64 into GNU C++)
     
  • Add libraries directory into Library Path tab.
    (Add /usr/lib/oracle/11.2/client64/lib)
     
  • Tell Eclipse which library that you want to link with your application by add needed libraries name into Libraries tab.
    In my situation, I need to add nnz11 (libnnz11.so in libraries directory), clntsh (libclntsh.so), occi (libocci.so).

Add OCCI libraries to Eclipse
Required OCCI libraries

We need only occi library but nnz11 and clntsh is required by occi, so we need to add them first. If not, Console may be throw an error the same as:

Console
  1. Invoking: GCC C++ Linker
  2. g++ -L"/media/Data/workspace/zie/lib/curl/lib" -L/usr/lib/oracle/11.2/client64/lib -o"zie"  ./src/zie.o  ./src/db/db.o   -locci
  3. /usr/lib/oracle/11.2/client64/lib/libocci.so: undefined reference to `OCISubscriptionDisable'
  4. /usr/lib/oracle/11.2/client64/lib/libocci.so: undefined reference to `ldxend'
  5. /usr/lib/oracle/11.2/client64/lib/libocci.so: undefined reference to `OCIPConvertRowidToUb1'
  6. ...
Hide/show line number

Additional, we need to set enviroment LD_LIBRARY_PATH = /usr/lib/oracle/11.2/client64/lib. It's important to let our library know another. We can set this environment by append two line into ~/.bash_profile file (you may need to logout and relogin after changing this file):

  1. LD_LIBRARY_PATH = /usr/lib/oracle/11.2/client64/lib
  2. export LD_LIBRARY_PATH
Hide/show line number

Remember, libraries is require to run our application and header file is require for development. If we have already finish all steps, we can create a demo project that connect to oracle database.

demo.cpp
  1. #include <iostream>
  2. #include "occi.h"
  3. #include <string>
  4.  
  5. using namespace std;
  6. using namespace oracle::occi;
  7.  
  8. int main() {
  9.         //Create new environment
  10.         Environment *env = Environment::createEnvironment(Environment::DEFAULT);
  11.         Connection *conn = NULL;
  12.  
  13.         try {
  14.                 string user     = "username";
  15.                 string passwd   = "password";
  16.                 string host     = "localhost";
  17.                 //Create new connection
  18.                 conn = env->createConnection(user, passwd, host);
  19.         }
  20.         catch(SQLException &e) {
  21.                 cout << e.getMessage();
  22.         }
  23.  
  24.         try {
  25.                 Statement *stmt = conn->createStatement("SELECT * FROM my_table");
  26.                 stmt->setPrefetchRowCount(32);
  27.                 ResultSet *rs = stmt->executeQuery();
  28.                 while(rs->next()) {
  29.                         cout << rs->getString(2) << endl;
  30.                 }
  31.         }
  32.         catch(SQLException &e) {
  33.  
  34.         }
  35.         return 0;
  36. }
Hide/show line number

If build successfully, we can sure that adding libraries and header step are ok. This demo project will execute query "SELECT * FROOM my_table".

Hope that help!