What is the difference between OCI and THIN driver connection with data source connection between java and oracle XE?

Question

I'm writing the below codes for connection between the java and Oracle 10g XE using 3 way(OCI, THIN and data source), the code is running successfully but don't know difference between the THIN and OCI with data source connection.

1-

public static void main (String args[]) throws SQLException
 {
  OracleDataSource ods = new OracleDataSource();
  ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
  Connection con = ods.getConnection();
  System.out.println("Connected");
  con.close();
 }

2-

public static void main(String args[])
     {
      try
      {
       // load oracle driver
      Class.forName("oracle.jdbc.driver.OracleDriver");
      // connect using Thin driver
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");
      System.out.println("Connected Successfully To Oracle");
      con.close();
      }
      catch(Exception ex)
      {
        ex.printStackTrace();
      }
 }

3-

public static void main(String args[])
     {
      try
      {
       // load oracle driver
      Class.forName("oracle.jdbc.driver.OracleDriver");
      // connect using Native-API (OCI) driver
      Connection con = DriverManager.getConnection("jdbc:oracle:oci:@","hr","hr" );
      System.out.println("Connected Successfully To Oracle using OCI driver");
      con.close();
      }
      catch(Exception ex)
      {
        ex.printStackTrace();
      }
 }

score 47 · Accepted Answer · edited Jul 15 '22 at 19:19

47

Oracle provides four types of drivers for their database, but I'll only enumerate the two you asked about.

The OCI driver is a type 2 JDBC driver and uses native code to connect to the database. Thus, it is only an option on platforms that have native Oracle drivers available and it is not a "pure" Java implementation.

Oracle's JDBC Thin driver is a type 4 JDBC Driver that uses Java sockets to connect directly to Oracle. It implements Oracle's SQL*Net TCP/IP protocol directly. Because it is 100% Java, it is platform independent and can also run from an Applet. (not that you should)

edited Jul 15 '22 at 19:19

nyg

2,380
3
25
40

answered Feb 11 '14 at 19:34

Elliott Frisch

198,278
20
158
249

Could please share some more info on OCI. Any good to read links would be great. – Suvasis Sep 06 '14 at 14:41
@suvasis click on oci in my answer, and scroll to the external links section. – Elliott Frisch Sep 06 '14 at 15:50
This link describes more on Programming and comparison to ProC concepts. I was asking if you have any link that would describe differences in terms of all settings we need configure both on application side and server side. More on implementation/development persepective. if any u have. – Suvasis Sep 06 '14 at 16:10
@Suvasis OCI is just the protocol the driver talks. There aren't settings to configure on the application side or the server side besides username, password, address, port and name (usually in tnsnames.ora). Basically, AFAIK, if you can connect it's correctly configured. – Elliott Frisch Sep 06 '14 at 16:20
AFAIK means ? u mean the only change in thin client and oci drive ruse is to replace "thin" with "oci" in the url connection string ? – Suvasis Sep 06 '14 at 16:28
As far as I know. Even the thin driver implements [OCI](http://en.wikipedia.org/wiki/Oracle_Call_Interface), only in pure Java. – Elliott Frisch Sep 06 '14 at 16:30
3

This contains some useful info : http://stackoverflow.com/questions/2896265/oracle-thin-driver-vs-oci-driver-pros-and-cons – tharindu_DG Dec 30 '14 at 12:11
@ElliottFrisch could you please, help me with just the name of **Oracle four types of drivers** ? Thanks – JRichardsz Nov 28 '21 at 23:44
@JRichardsz Seven year old question man, but sure. [JDBC Drivers](https://en.wikipedia.org/wiki/JDBC_driver) lists the four types as 1. JDBC-ODBC bridge (no longer distributed with new Java), 2. Native-API driver (OCI driver fits here), 3. Network-Protocol driver, 4. Database-Protocol driver (Thin driver) – Elliott Frisch Nov 29 '21 at 00:41
Thanks Elliot. I added your description in this [answer](https://stackoverflow.com/a/70148803/3957754) to help someone :) – JRichardsz Nov 29 '21 at 01:13

Jean de Lavarene · Answer 2 · 2020-03-30T12:48:26.677

10

Both the JDBC thin driver and the JDBC OCI driver speak the same network protocol. From the server standpoint there is no difference between the two. The JDBC thin driver is 100% Java and comes in a single standalone jar (some extra jars will be needed for advanced features). The JDBC OCI driver makes JNI calls to the OCI C client library and hence depends on the Oracle full client to be installed (OCI is also what sqlplus uses). Oracle recommends to use the JDBC thin driver which is what most customers use. It's the fastest driver and the most robust one.

edited Mar 30 '20 at 12:48

answered Dec 06 '17 at 22:48

Jean de Lavarene

3,461
1
20
28

_"Oracle recommends to use the JDBC thin driver[...]. It's the fastest driver and the most robust one."_ Do you have any source for that statement. I also believe that's the case, but I would need an official source to prove it, and I can't seem to find any. – LordOfThePigs Mar 21 '18 at 08:28
4

Oracle WebLogic Server only supports thin (not JDBC OCI). This means all the Oracle Fusion Apps use the thin driver. Moreover all the Oracle performance publications (SPecJ but also YCSB) use the JDBC thin driver. In all our OOW presentations we recommend to use the thin driver. BTW I work for Oracle and my group is responsible for the development of these drivers. Is that official enough? – Jean de Lavarene Mar 21 '18 at 08:38
1

Sounds good to me :-). I wish our DBAs would hear you out. But it seems that they don't like the thin driver due to how the failover configuration has to be done into the JDBC URL rather than through tnsnames.ora. Or is there a way to get the thin driver to read this file as well. As you can see, I'm not an expert at this, I'm just a developper that is super-annoyed at having to keep the Oracle client and drivers in sync all the time. I myself would much prefer to use the thin client. – LordOfThePigs Mar 22 '18 at 10:49
2

@LordOfThePigs Yes you can use tnsnames.ora with the thin driver. For that you need to set the driver know where to find the file by setting the property "oracle.net.tns_admin" either as a connection property or a Java system property (set with java -D). The value should be the directory where tnsnames.ora is located. Then the url is jdbc:oracle:thin:@. Contact me directly if you need help with this. – Jean de Lavarene Mar 22 '18 at 13:18
1

I just got some more feedback. I've been told that the thin driver does not support Transparent Application Failover (TAF), but only Fast Connection Failover (FCF). This document (http://www.oracle.com/technetwork/database/app-failover-oracle-database-11g-173323.pdf) seems to say so, but it is now 8 years old. Is that still true? I think this discussion is still relevant to the scope of this question. And the extra clarifications can probably be added to your answer later on. – LordOfThePigs Mar 23 '18 at 14:39
2

While it's true that TAF is only supported in the JDBC-OCI driver, starting in 12.2.0.1 the JDBC thin supports a more complete replay capability called Application Continuity (AC). We encourage all customers to use AC instead of TAF. – Jean de Lavarene Mar 26 '18 at 07:18

What is the difference between OCI and THIN driver connection with data source connection between java and oracle XE?

2 Answers2

Linked