ODBC VS OCI

ODBC Vs OCI

Differences between ODBC and OCI

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.

ODBC :- if we are using ODBC to connect Oracle source then we need to give ODBC connection in the machine where the BI Server installed on.
-- so, the machine which have BI Server that should contain ODBC connection.

OCI :-  

OCI is a Native Connection, with out having ODBC Connection in the machine we can import the data.

Ex :- Importing through Server.

OCI is a much better choice than ODBC for the following 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