Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: Connecting Pentaho to Oracle

  1. #1
    Join Date
    Feb 2016
    Posts
    22

    Default Connecting Pentaho to Oracle

    Hi,

    I am trying to connect Pentaho Kettle to Oracle database. I have downloaded the Oracle JRE files and placed all the files and folder in the lib folder of pentaho data-integration folder.

    There is error like below. I think this must be very common issue. I tried searching such issues in the forum but could not get. Hence posting and hoping a solution to it.

    Regards.. Jawed

    Error connecting to database [dd] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Driver class 'oracle.jdbc.driver.OracleDriver' could not be found, make sure the 'Oracle' driver (jar file) is installed.
    oracle.jdbc.driver.OracleDriver


    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Driver class 'oracle.jdbc.driver.OracleDriver' could not be found, make sure the 'Oracle' driver (jar file) is installed.
    oracle.jdbc.driver.OracleDriver

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You need Oracle JDBC driver not just the Oracle JRE.

    http://www.oracle.com/technetwork/da...ex-091264.html

  3. #3
    Join Date
    Feb 2016
    Posts
    21

    Default

    I have the same problem and I have already put the JDBC file in PDI folder.

    Do you know anything else that I can do?

    Thanks

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Where did you put what file?
    Details count here.

  5. #5
    Join Date
    Feb 2016
    Posts
    21

    Default

    I'm using PDI 6 and OracleXE 11

    I put "ojdbc6.jar" in the folder "C:\opt\data-integration\lib"

    And when I try to connect, It shows:

    Error connecting to database [POC] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database


    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor





    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database


    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor





    at org.pentaho.di.core.database.Database.normalConnect(Database.java:459)
    at org.pentaho.di.core.database.Database.connect(Database.java:357)
    at org.pentaho.di.core.database.Database.connect(Database.java:328)
    at org.pentaho.di.core.database.Database.connect(Database.java:318)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2734)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:588)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:136)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:389)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:470)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:457)
    at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3051)
    at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3021)
    at org.pentaho.di.ui.spoon.Spoon.access$2400(Spoon.java:347)
    at org.pentaho.di.ui.spoon.Spoon$27.widgetDefaultSelected(Spoon.java:6064)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1339)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9214)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:653)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor



    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:572)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:443)
    ... 46 more
    Caused by: 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.<init>(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:569)
    ... 47 more
    Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:399)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
    ... 54 more


    Hostname : localhost
    Port : 1521
    Database name : POC

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Your error (different than the error of the person who started this thread...) indicates that Oracle doesn't like to talk.
    Are you sure that your Oracle DB is configured with a SID of POC, or is it an net service name?

    If you aren't sure, try changing the DB name to /POC rather than POC. It might just work

  7. #7
    Join Date
    Feb 2016
    Posts
    22

    Default

    Hi,

    I have downloaded the Oracle JDBC driver. Its ojdbc6.jar. Placed in C:\pentaho_etl\data-integration\lib folder. Still I am also getting following error.

    Also tried with /MYDB as suggested below. Its same error.

    Error connecting to database [oracle_test] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor



    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    Hostname : 173.xx.yy.zz
    Port : 1522
    Database name : MYDB
    Last edited by gutlez; 02-25-2016 at 07:01 PM.

  8. #8
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by jaansari View Post
    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    IO Error: The Network Adapter could not establish the connection
    ...
    Caused by: java.net.ConnectException: Connection refused: connect
    ...
    Hostname : 173.xx.zz.yyy
    Port : 1521
    Database name : NETDEV
    I can't be certain, but this seems to be suggesting that from your computer that has PDI installed, a path to your DB server cannot be found. It's also possible that Oracle is not running at that address, or that you're not allowed to connect to there. None of these are PDI issues directly.

  9. #9
    Join Date
    May 2014
    Posts
    358

    Default

    Quote Originally Posted by dvalenti View Post
    I'm using PDI 6 and OracleXE 11
    Use "XE" as the database name.

  10. #10

    Default

    Hello jaansari,

    Quote Originally Posted by jaansari View Post
    Hi,

    I have downloaded the Oracle JDBC driver. Its ojdbc6.jar. Placed in C:\pentaho_etl\data-integration\lib folder. Still I am also getting following error.

    Also tried with /MYDB as suggested below. Its same error.

    Error connecting to database [oracle_test] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor



    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    Hostname : 173.xx.yy.zz
    Port : 1522
    Database name : MYDB
    As gutlez mentions, this is not a PDI error; it is an Oracle error.

    It says that the Oracle database ID (SID) you have used when configuring the connection in PDI is wrong. Make sure you're using the right SID. You can try it out by trying to connect to the DB using sqlplus:
    Code:
    sqlplus user/password@sid
    Obviously, user & password are credentials to your Oracle DB and sid is your Oracle DB ID. If you can connect with sqlplus from the same host where PDI is running you should have no problem to connect with PDI. If sqlplus fails, you should look deeper and find out the reason for the error.

    And, again, this is not PDI related anyway.

    Hope this helps.

    --
    Julio.

  11. #11
    Join Date
    Feb 2016
    Posts
    21

    Default

    Quote Originally Posted by Lukfi View Post
    Use "XE" as the database name.
    Thank you Lukfi, that works!

  12. #12
    Join Date
    Feb 2016
    Posts
    22

    Default

    Hi I am able to connect using the following command
    sqlplus user/password@sid

    sqlplus <user>/<password>@MYDB

    ---
    So SID is MYDB in this example right. I am using the same i.e. MYDB as Database Name in PDI. Hoping I am doing correct but still no luck.

    Regards.. Jawed

  13. #13
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Jaansari, Just for giggles, try connecting to DB Name "XE" (without quotes)
    Last edited by gutlez; 02-25-2016 at 07:06 PM.

  14. #14
    Join Date
    Feb 2016
    Posts
    22

    Default

    Hi,

    As suggested tried to connect with XE database name and the error is as follows

    Error connecting to database [oracle_test] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor



    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database

    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor



    at org.pentaho.di.core.database.Database.normalConnect(Database.java:459)
    at org.pentaho.di.core.database.Database.connect(Database.java:357)
    at org.pentaho.di.core.database.Database.connect(Database.java:328)
    at org.pentaho.di.core.database.Database.connect(Database.java:318)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2734)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:588)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:136)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:389)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:470)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:457)
    at org.pentaho.di.ui.spoon.Spoon.newConnection(Spoon.java:8750)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.jface.tags.JfaceMenuitem.access$100(JfaceMenuitem.java:43)
    at org.pentaho.ui.xul.jface.tags.JfaceMenuitem$1.run(JfaceMenuitem.java:106)
    at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
    at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:545)
    at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:490)
    at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:402)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1339)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9214)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:653)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:572)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:443)
    ... 55 more
    Caused by: 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:458)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:187)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:569)
    ... 56 more
    Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
    ... 63 more

    Hostname : 173.xx.yy.zz
    Port : 1522
    Database name : XE

  15. #15

    Default

    Hello jaansari,

    Firstly, as we have mentioned a couple of times in this same thread, this is not a PDI related error. Any DB connection error where you can see "ORA-xxxxx" is coming directly from Oracle, so you should find out the reason and solution with Oracle documentation or support/forums. Any advice people is giving out in this thread is because of their experience with Oracle, but it's not an error within PDI.

    Secondly: As it has been pointed out, "XE" is the instance name that is installed by default with Oracle XE (express edition). If your DB is Oracle XE, and "XE" is not working, then the instance name may have been changed by you/your DBA. If your DB is not Oracle XE, then the instance name may be whatever.

    Try asking first your DBA about the SID for that database.

    Alternatively, you may try to find out the SID by trying these two things (from https://community.oracle.com/thread/...art=0&tstart=0 ):

    1) From the OS / command prompt, depending on whether your DB is on Windows/Linux:
    ============
    Windows
    =======
    Check Windows Service Name. It will be something like:

    OracleServiceDB10G

    DB10G is your SID

    UNIX
    ====

    ps -ef | grep pmon

    ============

    2) If you can connect to the DB, you can query v$instance and find out the instance_name.


    HTH,

    --
    Julio.

  16. #16
    Join Date
    Feb 2016
    Posts
    22

    Default

    Thank you very much to everyone

    Yes instance name was different, found out it and I am to connect now.

    Appreciate your help in this regard.

    Regards.. Jawed

  17. #17
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Was the Instance name MYDB as you kept telling us it was?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.