KETTLE5.4ODBC and OCI Connection configuration

1.kettle 5.4 Error when using JDBC Connection (test a different database, found that only connect to 11gRAC will report JDBC errors)

The specific error is as follows

Java.sql.sqlexception: exception occurred during database connection creation:
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@90.12.xx.xx:1521:orcl
ORCL_CON
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
90.12.xx.xx:1521:orcl 

Use Sqlplus to further determine that the database function is normal and try other connection methods.

2. try to connect using ODBC :

Management Tools -Data Source--Userdsn--Add--SelectOracleDrive

in theKettleon the configurationODBCConnection Testok!

3. try the OCI Connection method error is as follows:

Error connecting to database [source] DB: 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)
no ocijdbc11 in java.library.path
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)
no ocijdbc11 in java.library.path
at org.pentaho.di.core.database.Database.normalConnect(Database.java:428)
at org.pentaho.di.core.database.Database.connect(Database.java:358)
at org.pentaho.di.core.database.Database.connect(Database.java:311)
at org.pentaho.di.core.database.Database.connect(Database.java:301)
at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
Host name:
Port: 1521
Database name: orcl 

4.Google has taken the following solutions:

Set in the environment variable:

ORACLE_HOME=D:\app\gssjcj\product\11.2.0\dbhome_1
TNS_ADMIN=D:\app\gssjcj\product\11.2.0\dbhome_1\NETWORK\ADMIN
PATH=%ORACLE_HOME%\BIN;%PATH% tnsnames.ora contains:
J3_CX = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 90.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Copied OCIJDBC11.dll to the libswt\win64

ConfigurationOCIConnection:

Test ok!

Later in http://community.pentaho.com/see the following description:

oci

oci uses the Oracle client installed on The client you ' re currently using. If you is using OCI and an Oracle NET8 client, the JDBC driver version used in kettle needs to match your Oracle client V Ersion. PDI 2.5.0 shipped with version 10.1, 3.0.0 ships with version 10.2. You can either install this version of the Oracle client or   change the JDBC driver in PDI If versions don ' t match up .

this is what you change the Oracle JDBC Driver in Kettle. Replace files "Ojdbc14.jar" and "Orai18n.jar" in the directory Libext/jdbc of your distribution with the files found in th E $ORACLE _home/jdbc directory on your server or if the versions is different, with the JDBC driver that matches your NET8 Client. For Oracle 11g The drivers is named Ojdbc5.jar and ojdbc6.jar.

if you still has issues please remember That the DLL, the Oracle uses to connect have to is in your path. If all else fails, try copying the Ocijdbc10.dll (might is called different in different versions) to the Libswt/win32 fol Der (or Win64).

RAC (Real application Cluster)

When dealing with a Real application Cluster or other complex failover Oracle situations, please define the connection lik E this:

* Set to Native (JDBC) connection type

· * Leave hostname and port empty (this was also working with a port setting of-1)

·          * Set The database name to something ...
(DESCRIPTION = (address = (PROTOCOL = tcp) (host = HOST1-VIP) (PORT =1521)) (address = (PROTOCOL = tcp) (host = HOST2-VIP) (POR T = 1521)) (load_balance= Yes) (Connect_data = (SERVER = dedicated) (service_name =db-service) (Failover_mode = (TYPE = SELECT ) (METHOD = BASIC) (retries = delay= 5 ))))

or (description= (address_list= ( Address= (PROTOCOL=TCP) (Host=primary_node_hostname) (port=1521)) (Address= (protocol=tcp) (HOST=SECONDARY_NODE_ HOSTNAME) (port=1521)) (Connect_data= (Service_name=database_servicename)))

or (description= (Failover=on) (address_list= (Load_balance=on) (address= (PROTOCOL=TCP) (host=xxxxx) (PORT=1526)) ( Address= (PROTOCOL=TCP) (host=xxxx) (port=1526))) (Connect_data= (SERVICE_NAME=SOMESID)))

Note:this does with arepository based system until 3.0.4. Beginning with 3.0.5 and 3.1 file Basedsystems does also support this.

KETTLE5.4ODBC and OCI Connection configuration