Difference between ODBC and OCI connections

This is basic as we all are aware that OBIEE supports both ODBC and OCI, most importantly we can use both to connect to a Oracle DB. But oracle suggests us to use OCI when we connect to a Oracle DB, and surprisingly we do it. The big question here is WHY ?

Let us now see what each of them are and then how they are different.

ODBC:- Open DataBase Connectivity, which is known as Universal Data Connector.

OCI:- Oracle Call Interface,Normally connects to Oracle Source.

Hence, both can connect to Oracle Sources.If we are using ODBC to connect Oracle source then we need to give ODBC connection details in the machine from which we are connecting. However OCI is a Native Connection, which doesnt require having ODBC Connection in the machine.

To the question WHY OCI ? following are the reasons:

• OCI is optimized for queries. Transparent prefetch buffers reduce round-trips and improve performance and scalability. As a result, there is reduced memory usage on the server.
• OCI is optimized for round-trips. No-reply requests are batched until the next call is generated for the server. This allows certain calls to be lazily propagated.
• OCI is thread safe. You do not need to mutex (use mutual exclusivity locks) any of the OCI handles. ODBC is not thread safe, so you have to mutex most data structures.
• OCI provides an asynchronous event notification API for active databases.
• OCI provides enhanced array data manipulation language (DML) operations that reduce round-trips.
• OCI returns ROWIDs for all rows selected for update in a single round-trip. Using ROWID allows for more efficient SQL access.
• ODBC has no concept of sessions. OCI decouples connections, sessions and transactions. Multiple users can use a single connection; they get serialized on the connection for SQL operations. Multiple transactions can exist per user. This allows users to scale and service more users than there are connections available. Sessions and transactions can be migrated between connections to the same server.
• ODBC does not support object types, large objects (LOBs), and other new Oracle datatypes.
• ODBC affects server scalability. Using ODBC and having n number of concurrent users forces the server to have n number of processes service the clients if Oracle8i is operating in dedicated server mode. Your operating system may or may not support so many connections and processes.
• ODBC is a wrapper around OCI so it is slower.