Oracle OCCI for Visual Studio 2010

I wrote a sort of hello world application using OCCI for Oracle 11 and VC++ 2010. A simple example that runs a select statement on the standard Oracle HR schema and print on the console the resulting rows. If you have a previous experience developing with Oracle OCI you would certainly appreciate the difference.

I'd say the development team has made a good job, but still both the setup and the usage require a bit of attention.

Firstly, I warmly suggest you to use Oracle Database Instant Client, since it makes much easier the job. To be used with VC++ 9 and 10, you need also to download a patch.

Secondly, you should ensure your application has a way to access the tnsnames.ora file where is defined the SID for the database you want to access; you can do that ensuring that ORACLE_HOME or TNS_ADMIN is defined as environment variable, and it is associated with the right path.

If ORACLE_HOME is used, it should be something like c:\ ... \oracle\product\11.2.0\server, where under server you should have network\admin, and in admin you should find tnsnames.ora - TNS_ADMIN should be the path containing tnsnames.ora.

Third step is about setting the Property Pages for the VC++ project that is going to actually use OCCI.

In the Configuration Properties:

1) C/C++
a) General - Additional Include Directories
Add the include directory for OCI. You should find it in you Oracle 11 server installation, something like ... \server\oci\include.
b) Code Generation - Runtime Library
Ensure ist value is set to /MDd or /MD, meaning Multithreaded (Debug) DLL.

2) Linker
a) Additional Library Directories
It should point to the directory where you put the VC++ 10 patch for the instant client.
b) Input - Additional Dependencies
Here you add the library, oraocci11.lib or oraocci11d.lib, specific for VC++ 10 and Oracle 11.

Fourth step is about making available instant client DLLs (generic and specific ones), adding their paths to the PATH environment variable, or copying them in the directory where the executable resides.

Once we have done all this stuff, it is relatively easy writing the code we talked about at the beginning of the post:

#include <iostream>
#include <occi.h>

namespace oc = oracle::occi;

int main()
{
   try
   {
      // setup
      oc::Environment* env = oc::Environment::createEnvironment(); // 1
      oc::Connection* conn = env->createConnection("hr", "password", "MYDB"); // 2
      oc::Statement* stmt = conn->createStatement("select * from countries where country_name like 'A%'"); // 3

      // execution
      oc::ResultSet* res = stmt->executeQuery(); // 4
      while(res->next()) // 5
         std::cout << res->getString(1) << ' ' << res->getString(2) << ' ' << res->getInt(3) << std::endl;

      // cleanup
      stmt->closeResultSet(res); // 6
      conn->terminateStatement(stmt);
      env->terminateConnection(conn);
      oc::Environment::terminateEnvironment(env);
   }
   catch(const oc::SQLException& e) // 7
   {
      std::cout << "Exception: " << e.what() << std::endl;
   }

   system("pause");
}

1. All the connections are created in an environment, that could have different setups. Here, we create a plain environment, that is enough for the current task.
2. Given an environment, we can create a connection. The third parameter is the SID identifying the database in the tnsnames.ora file, the first two parameters represent user and password.
3. Once we have a connection, we can create a statement in it. Here it is a select query.
4. When the statement is a query, we can execute it, getting back as a result a resultset.
5. Now we can loop on the resultset to access all the found rows.
6. Inverting the construction order, we cleanup the object we created: resultset, statement, connection and, finally, the environment.
7. Catching the exceptions that our OCCI calls could throw.

Not bad, if you compare this code with what you should write to get the same result using OCI. But it is a bit painful to see all these pointers, and thinking what happens in case of an exception, say, just after [3.] - the answer is: no cleanup function is called. A bit of RAII would help to make it more robust.