Oracle C++ OCI Database Example

Aim
The aim of this C++ tutorial is to create a simple client that uses Oracle C++ OCI (OCCI Oracle C++ Call Interface) to connect to an Oracle database. We will be using the windows command line C++ compiler cl.exe that comes with Visual Studio C++ for this example. This demonstrates connectiong to the Oracle database and manipulating data. Leave any questions or problems as comments and I will endeavour to answer them.

If you would like to see a database pool implemented for Oracle OCCI the please visit Oracle C++ OCI Database Connection Pool.

Assumptions
This article assumes that you have an Oracle database installed and running and you have downloaded and installed/unzipped the Oracle Client which contains the header files and libs required. For purposes of this example the the project directory is OracleOCCIExample. Also you have VC++ installed and configured. See Problems and Solutions Installing VC++ Express Edition, to install and run vcvars32.bat.

Versions used in this example
Sofware/ComponentImage
Windows XP SP2N/A
Oracle Client10201_client_win32.zip
Visual Studio Express Editions 2008 VC++N/A
Links to these files can be found here

Oracle client is installed (expanded) in the "D:\oracle" directory for this example. Please remember to substitute your own directories for these.

NOTE: OCCI uses tnsnames.ora to connect to the database. So make sure you have this file configured with the database details you wish to connect to.


Write and compile the Client
  1. Write the client and save it as oracleexample.cpp in your working directory.
     1. #include <iostream>
     2. #include <occi.h>
     3. 
     4. using namespace std;
     5. 
     6. int main(){
     7. 
     8.     oracle::occi::Environment* environment;
     9.     oracle::occi::Connection *con;
    10.     oracle::occi::Statement* stmt;
    11.     oracle::occi::ResultSet* res;
    12. 
    13.     try{
    14.     
    15.         environment = oracle::occi::Environment::createEnvironment(oracle::occi::Environment::DEFAULT);
    16.         con = environment->createConnection("gldbuser", "gldbuser", "MYDATABSE");
    17. 
    18.         stmt = con->createStatement("select * from example");
    19.         res = stmt->executeQuery();
    20.         
    21.         while (res->next())
    22.             std::cout<<res->getInt(1)<<"  "<<res->getString(2)<<std::endl;
    23.         
    24.         stmt->closeResultSet(res);
    25.         con->terminateStatement(stmt);
    26.         environment->terminateConnection(con);
    27. 
    28.     }catch(oracle::occi::SQLException &e){
    29.         std::cout<<e.what();
    30.     }
    31. 
    32.  return 0;
    33. }
    Hide line numbers

  2. Open a prompt to the working directory and compile the code using the windows cl.exe compiler. Make sure to point '-I' (capital i) option to the include files and to include mysqlcppconn.lib

    ..workspace\OracleOCCIExample>cl -o oracletest.exe oracleexample.cpp -I D:\oracle\product\10.2.0\client_1\oci\include D:\oracle\product\10.2.0\client_1\oci\lib\msvc\oraocci10.lib
    Substitue your local directories.

  3. This should now create the ocalceexample.exe file. However if you run it now you might get some errors saying that some DLLs are missing.

  4. Add these paths to your existing path so that the executable can find the required libraries. Again, don't forget to replace the directories with the ones on your local machine. Basically you need to give the 'bin' diretory of your MySQL database installation and the 'lib' directory in your MySQL Connector C++ directory.


    ..\worksapce\OracleOCCIExample>set path=%path%;D:\oracle\product\10.2.0\client_1\oci\lib\msvc\vc7

    ..\worksapce\OracleOCCIExample>set path=%path%;D:\oracle\product\10.2.0\client_1\BIN

  5. Now run the oracleexample.exe and you should see the result. The most likely errors are errors stemming from problems with the tnsnames.ora and maybe DLL entry points when you run the program. To fix the DLL entry points you can set your classpath only to the paths specified above, to make sure you are not picking up any other dll.

20 comments:

Anonymous said...

Hi,

During compilation this script I've a error error LNK2019: unresolved external symbol "public: static class oracle::occi::Environment * __cdecl oracle::occi::Environment::createEnvironment(enum oracle::occi::Environment::Mode,void *,void * (__cdecl*)(void *,unsigned int),void * (__cdecl*)(void *,void *,unsigned int),void (__cdecl*)(void *,void *))" (?createEnvironment@Environment@occi@oracle@@SAPAV123@W4Mode@123@PAXP6APAX1I@ZP6APAX11I@ZP6AX11@Z@Z) referenced in function _main.

Do you know how I can to resolve this problem?

Marek

righteous said...

Try installing the free SQL Server express edition. This should refresh all you libraries.

Anonymous said...

You need to add oraocci11.lib (or whatever version you are using as an additional dependancy. In the project properties, this is at Linker->Input->Additional Dependancies

righteous said...

yes, you can try compiling with oraocci11.lib

Anonymous said...

Hi,

When i try to run above program , it crashes randomly, have you see such behaviour before if yes ..what was the solution.

Thanks in advance

Naveen

Jovke said...

Your executable file needs to find oracle DLLs also. They must be in path or you can just copy it to the same directory where .exe file is.

righteous said...

Absolutely.

Better to set the path the oracle DLLs rather than copy them to the directory of your executable

MAHESH KUMAR SANKA said...

hai,
any one can help me.i am using solaris and oracle .i too facing same problem.how to resolve the issue

righteous said...

What is the problem you are facing?

Can you be a bit more descriptive? Is it that the linker is failing to find the libraries?

MAHESH KUMAR SANKA said...

while i am trying to compile
i am getting the error
oracle::occi::Environment::createEnvironment(oracle::occi::Environment::Mode, void*, void* (*)(void*, unsigned int), void* (*)(void*, void*, unsigned int), void (*)(void*, void*))

sorry for late reply

MAHESH KUMAR SANKA said...

Hai righteous
i am defining my problem more clearly
when compiling
error is:
undefined first reference
symbol in file
Oracle::occi::Environment::createEnvironment(std::basic_String>const&,std::basic_string,std::allocator>const&,oracle::occi::Environment::Mode,void*,void*(*)(void*,unsigned int),void*(*)(void*,void*,unsigned int),void(*)(void*,void*))

Anonymous said...

I m also getting the error
oracle::occi::Environment::createEnvironment(oracle::occi::Environment::Mode, void*, void* (*)(void*, unsigned int), void* (*)(void*, void*, unsigned int), void (*)(void*, void*))
plz help me

Krish Rajendran said...
This comment has been removed by the author.
Krish Rajendran said...
This comment has been removed by the author.
Krish Rajendran said...

hi

Krish Rajendran said...

when u compile this source u have to import library[clntsh and occi] and header files

g++ oracleexample.cpp -I /home/epic/Desktop/db_clint_connection/public -L /home/epic/Desktop/db_clint_connection/lib -l clntsh -l occi

that files are automatically stored same location, when u installed oracle instance client.

u can download instance client from there
http://www.oracle.com/technetwork/topics/zlinuxsoft-096525.html

Anonymous said...

Thanks a lot for the sample Example..........!

Anonymous said...

Thanks for the above program and I struggled to compile the above code. Each time I am getting the below error while comiling with

g++ -o exe oci.cc -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/rdbms/lib -locci -lclntsh
/usr/bin/ld: cannot find -locci
collect2: ld returned 1 exit status

I need to use above command becuase occi.h file is in location $ORACLE_HOME/rdbms/public

And found a solution for this prolem, use the below one for compilation but do export the ORACLE_HOME
g++ -o exe oci.cc -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/rdbms/lib -L$ORACLE_HOME/lib -locci -lclntsh

Krish Rajendran said...

so u stop firewall and SELinux. after that compail and run it will compailed

Naviya Nair said...

Very interesting and good Explanation
ASP NET Training
ASP NET Training
ASP NET Online Training
C-Sharp Training
Dot Net Training in Chennai
Online .Net Training


MVC Training
WCF Training
Web-API Training
LINQ Training
Entity Framework
Training

Dot Net Interview Questions