How to connect Eclipse to Oracle database - Step by Step Guide

Though I prefer Toad or Oracle SQL Developer tool to connect Oracle database, sometimes it's useful to directly connect Eclipse to Oracle using JDBC using its Data Source Explorer view. This means you can view data, run SQL queries to the Oracle database right from your Eclipse window. This will save a lot of time wasted during switching between Toad and Eclipse or Oracle SQL Developer and Eclipse. Eclipse also allows you to view Execution plan in both text and Graphical mode, which you can use to troubleshoot performance of your SQL queries. In this article, I'll tell you steps to connect Eclipse to Oracle database. Since Eclipse uses Java to connect to Oracle database, it asks for JDBC driver information. Since connecting using thin Oracle JDBC driver is much easier, as you just need to drop a JAR file in the classpath, we will be using JDBC thin driver to connect Oracle from Eclipse. Depending upon your Oracle version e.g. Oracle 10g or Oracle 11g you need to download Oracle JDBC thin driver e.g. ojdbc14.jar file. Just download this JAR file and configure it on Eclipse IDE. Enter username, password, and sid and you are ready to execute SQL query to Oracle database from Eclipse.


In order to connect to Oracle database instance, you need two things:

1) Eclipse IDE for Java EE developers, because that contains the database development plugin which doesn't come along with Eclipse IDE for Java developers. You can download the latest version of Eclipse IDE for Java EE developers here.

2) Oracle JDBC JAR, since Eclipse uses JDBC to connect to Oracle database it needs JDBC driver JAR for Oracle. You can use any type of JDBC driver, but I prefer Oracle JDBC thin driver e.g. ojdbc14.jar file to connect to Oracle 11g instance. If you don't have, you can download from here.

Once you have these two things, we are ready to connect to any local or remote Oracle database instance from Eclipse IDE.   I am using Eclipse Java EE IDE for Web Developers, Version: Kepler Service Release 2.



Steps to connect Oracle database from Eclipse

The steps are exactly similar to what I have shown you before while connecting to Microsoft SQL Server from Eclipse IDE, here, the only difference is, this time, we are choosing Oracle from the list of database and installing Oracle JDBC driver instead of SQL Server JDBC driver.

Here are the exact steps to connect Oracle instance from Eclipse:
  1. Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
  2. Create Connection Profile, Chose Oracle
  3. Choose JDBC Driver and specify its location
  4. Specify connection detail e.g. host, port, username, and password
  5. Test Connection

Tips like this save a lot of time and improve productivity and that's why I have always said that good understanding of Eclipse IDE is must to become an expert Java J2EE developer and developer should spend some time to learn their tools e.g. Eclipse.

If you are primarily a Java EE developer then you can also take a look at Ram Kulkarni's Java EE Development with Eclipse book, which will help you to write code, debug, test, and troubleshoot Java EE 7 applications right from the Eclipse IDE.




Now, let's see each step in little bit detail with screenshot if you are ready to connect to Oracle database from Eclipse, follow it along:

Step 1: Open Eclipse IDE and Select Database Perspective
In order to open the Database Development perspective go to Windows >> Open Perspective >> Other >> Database Development, as shown below:

How to connect Eclipse to Oracle database



Step 2:  Create Connection Profile
For creating a new connection profile, select the "Database Connection" at the top left in the Database Development view, right click and select "New Connection". This will open the New Connection Profile window as shown below. Now type Oracle and give your connection profile a meaningful name e.g. combination of application, host, and instance.

Steps to connect Eclipse to Oracle 11g



Step 3: Choose JDBC Driver and specify its location
Once you have selected the database, it's time to select the JDBC driver. To configure JDBC driver, just click the setting icon on the previous screen as shown below:

Connect to Oracle 11g from eclipse using JDBC

After that, the following screen will open which will list down all JDBC drivers for supported Oracle versions e.g. Oracle 10g, Oracle 11g, and JDBC thin driver or other drivers. I have chosen the Oracle Thin Driver to connect Oracle 11g as seen below:

JDBC driver JAR for Oracle 11g


Once you select a particular driver, you can see the error message appear at the top saying "Unable to locate JAR/ZIP in the file system as specified by the driver definition: ojdbc14.jar", this means you need to locate the path of this JAR in your local file system, as shown below. If you don't specify the correct JAR, you will get "java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver" error.


Once you are done, click Ok and then it will take you to the previous screen. Click next there.


Step 4:  Specify Database Connection details
Now, it's time to specify the database connection details e.g. host, port, sid, username, and password required to connect to an Oracle database instance.



Step 5: Test Connection
Once you are done with putting connect detail, just click the "Test Connection" button, if everything is good then you will see a ping succeed message, if not then you will see a "ping failed" message.


That's all about how to connect Eclipse to Oracle database using Java and JDBC. This is an immensely helpful tip if your application is using Oracle database at the backend and you frequently need to select, update or delete data.

Further Learning
The Eclipse Guided Tour - Part 1 and 2
Java Web Fundamentals
The Java Developer's Guide to Eclipse

9 comments:

  1. Wow! It's really work. Thanks Javin for such a useful document. I tried for a connection and successfully connected eclipse to oracle database.

    ReplyDelete
  2. after clicking add zar/zip on ojdbc14 a new filename is asked in my installation..what should i do?

    ReplyDelete
    Replies
    1. @Unknown, can you please elaborate? When you click add JAR option a dialog will open and you just need to choose the ojdbc14.jar and upload, that's it. It will then add the above mentioned JAR in the classpath.

      Delete
  3. I am receiving below error after clicking test connection
    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)

    ReplyDelete
  4. After clicking on test connection I am receiving below error:
    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)

    ReplyDelete
    Replies
    1. Hello @Abhinav, check if you are using correct SID and if that exists in Oracle server. Talking to a DBA may help.

      Delete
  5. How and where to find Sid,host, port etc

    ReplyDelete
    Replies
    1. You can find that from your Oracle instance of Oracle DBA.

      Delete
  6. Hi Javin,
    I am new to Oracle as well as Eclipse..
    I have Oracle 11g XE and Eclipse Oxygen installed on my Windows 10 OS.
    I am trying to connect to the database (using your guide) but keep on getting ping failed while testing connection. the details of the error are as below:
    java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:414)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
    at org.eclipse.datatools.enablement.internal.oracle.JDBCOracleConnectionFactory.createConnection(JDBCOracleConnectionFactory.java:27)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)

    can you help in resolving this?
    Thanks in advance
    Prasad

    ReplyDelete