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 the Execution plan in both text and Graphical mode, which you can use to troubleshoot the performance of your SQL queries.
In this article, I'll tell you the steps to connect Eclipse to the Oracle database. Since Eclipse uses Java to connect to the Oracle database, it asks for JDBC driver information. Since connecting using a 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 like ojdbc14.jar file. Just download this JAR file and configure it on Eclipse IDE. Enter the username, password, and sid and you are ready to execute the SQL query to the Oracle database from Eclipse.
In order to connect to the 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 the Oracle database it needs JDBC driver JAR for Oracle. You can use any type of JDBC driver, but I prefer Oracle JDBC thin driver like ojdbc14.jar file to connect to Oracle 11g instance. If you don't have one, you can download it 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.
Here are the exact steps to connect the Oracle instance from Eclipse:
Tips like this save a lot of time and improve productivity and that's why I have always said that a good understanding of Eclipse IDE is a must to become an expert Java J2EE developer and developers should spend some time learning 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 of detail with a screenshot if you are ready to connect to the 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:
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.
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:
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:
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.
In order to connect to the 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 the Oracle database it needs JDBC driver JAR for Oracle. You can use any type of JDBC driver, but I prefer Oracle JDBC thin driver like ojdbc14.jar file to connect to Oracle 11g instance. If you don't have one, you can download it 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 databases and installing Oracle JDBC driver instead of SQL Server JDBC driver.Here are the exact steps to connect the Oracle instance from Eclipse:
- Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
- Create Connection Profile, Chose Oracle
- Choose JDBC Driver and specify its location
- Specify connection detail e.g. host, port, username, and password
- Test Connection
Tips like this save a lot of time and improve productivity and that's why I have always said that a good understanding of Eclipse IDE is a must to become an expert Java J2EE developer and developers should spend some time learning 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 of detail with a screenshot if you are ready to connect to the 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:
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.
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:
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:
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.
Wow! It's really work. Thanks Javin for such a useful document. I tried for a connection and successfully connected eclipse to oracle database.
ReplyDeleteI didn't find database development..what can I do??
Deleteafter clicking add zar/zip on ojdbc14 a new filename is asked in my installation..what should i do?
ReplyDelete@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.
DeleteI am receiving below error after clicking test connection
ReplyDeletejava.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)
After clicking on test connection I am receiving below error:
ReplyDeletejava.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)
Hello @Abhinav, check if you are using correct SID and if that exists in Oracle server. Talking to a DBA may help.
Deleteyou need to use service name not sid with later databases from oracle (18, 19 +)
DeleteHow and where to find Sid,host, port etc
ReplyDeleteYou can find that from your Oracle instance of Oracle DBA.
DeleteHi Javin,
ReplyDeleteI 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
Hi prasad may I know how this issue of you has been resolved?
DeleteHow to create a java database connection with oracle using the oracle thin driver but with : the service name instead of SID. I can on netbeans but not in Eclipse ?????
ReplyDeleteI got ping failed several times and searched according to another website
ReplyDeletethe tnsnames.ora file in the Oracle installation directory, it helped me to find the port, sid and host name to configure Eclipse.
how to write xml file to establish a connection to Oracle 11g in web dynamic project
ReplyDeletehi what is SID,Username,Password...? (i am doing it for the first time)
ReplyDeleteHello Unknown, SID is oracle specific Id for a database instance and username, password are your authentication credentials to login into database. Please check with your team mates or DBA about that, they will have the details you need to login.
DeleteThe Network Adapter could not establish the connection
ReplyDeleteHello Pavani, most likely the hostname and port combination is wrong in your case. Please check if Oracle database is actually up and listening on that port. You can use telnet command to check that.
DeleteI have mysql in my windows os but how to connect to eclipse ide
DeleteHi , I am using ojdbc6.jar file and trying to insert some data. it was showing error like sql did not end properly.
ReplyDeleteInsert into mns sel ids from mns_19; Can you please suggest me which ar file support this insert statement.
I m not getting the database development option(Windows >> Open Perspective >> Other >> Database Development).I have added jar file from the library. what should i do now.
ReplyDeleteWhich version of Eclipse are you using? Also are you using Java or J2EE one?
DeleteI have created database table named as admin_login and inserted id, username and password with 2 records.. and create a connection using servlet but can not getting the values in database table :
ReplyDeletesee my servlet connection code below :
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("test2");
try{
response.setContentType("text/html");
PrintWriter pw = response.getWriter();
System.out.println("under try");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");
Statement st=con.createStatement();
System.out.println("connection established successfully...!!");
String sql = "select * from admin_login6";
ResultSet rs=st.executeQuery(sql);
System.out.println(rs.getRow()); // i get 0 rows
while(rs.next())
{
System.out.println("under while");
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
}catch(SQLException e) {
System.out.println(e);
}
catch(ClassNotFoundException e) {
System.out.println(e);
}
}
you can double check your table name, select * From admin_login6 while you mentioned that your table name is "admin_login" then why "6", also you can direclty run your query first in SQL console like SQL Developer or TOAD for Oracle, Java code looks correct.
DeleteHi, I had got test connection successful. still getting Error while running program:
ReplyDeleteNo suitable driver found for jdbc:oracle:thin:
That's strange, I think there is some difference in the driver name, double check that.
DeleteHi,
ReplyDeleteI am using Windows 10 64 bit laptop,I am trying to connect Oracle 11g XE to Eclipse IDE Enterprise Java, i followed the steps as mentioned on your site, as follows :
Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
Create Connection Profile, Chose Oracle
After selecting Oracle option from the list i click next and then i see most of the options faded, i click on the small circle option next to driver's bar, i see the option to add JAR file but all those tabs are faded/unclickable
When i download the JAR file for Oracle 11G XE, do i need to relocate to certain folder? or am i missing any step which is why the eclipse is unable to detect the Oracle drivers?
Please Help ..
-Thanks,
Yahya