配置Pentaho数据集成连接到Oracle数据库。

Question

我已经配置了带有数据库、用户和填充了小型数据库的Oracle VirtualBox。在同一台机器上，我安装了Pentaho Data-Integration（Spoon）和JDBC驱动程序，但是我尝试创建到Oracle数据库的连接时失败了。以下是我输入凭据/数据的位置： 主机名：localhost 数据库名称：(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL))) 端口号：1521 用户名：SYSTEM 密码：oracle 这是错误消息：

Error connecting to database [oraConn12c] : 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)
IO Error: NL Exception was generated TNS-04605: Invalid syntax error: Unexpected char or LITERAL "(" before or at  ( DESCRIPTION =


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)
IO Error: NL Exception was generated TNS-04605: Invalid syntax error: Unexpected char or LITERAL "(" before or at  ( DESCRIPTION =


    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 
.......
... more  .....
Caused by: java.sql.SQLRecoverableException: IO Error: NL Exception was generated TNS-04605: Invalid syntax error: Unexpected char or LITERAL "(" before or at  ( DESCRIPTION =
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
    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)
    ... 52 more
Caused by: oracle.net.ns.NetException: NL Exception was generated TNS-04605: Invalid syntax error: Unexpected char or LITERAL "(" before or at  ( DESCRIPTION =
    at oracle.net.resolver.AddrResolution.resolveAddrTree(AddrResolution.java:733)
    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:493)
    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
    ... 58 more

Hostname       : localhost
Port           : 1521
Database name  : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))

如何配置才能连接Pentaho/Spoon到Oracle数据库？Oracle数据库似乎工作正常，我也尝试了其他我创建的用户/数据库，但结果都一样。

- Alg_D

---

我认为你应该只在那个字段中放置数据库的名称。协议、主机和端口已经被定义了。 - bolav

尝试移除端口号。我认为你不需要端口号。 - Rishu Shrivastava

Answer 1

我曾经遇到同样的问题，通过从连接窗口中删除“主机名”和“端口号”的详细信息来解决。只保留“DB名称”字符串。

- Helton Costa

---

我也是。这样我就成功通过了连接测试，但是当我使用配置的数据源时，会出现“IO错误：NL异常已生成”。有什么想法吗？ - eradan

Answer 2

我曾经遇到过类似的问题。虽然我能够解决客户端工具的问题，但是卡特服务器的问题无法解决。

我通过JNDI连接来修复它。

将以下内容放入您的pdi\simple-jdni\jdbc.properties文件中。

mydb/type=javax.sql.DataSource
mydb/driver=oracle.jdbc.driver.OracleDriver
mydb/url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS(PROTOCOL=TCP)(HOST=myhost)(PORT=myport)))(CONNECT_DATA=(SERVICE_NAME=myservicename)))
mydb/user=myuser
mydb/password=mypassword

以及在您的repositories.xml文件中添加这个连接：

<connection>
<name>JNDI</name>
<server>myserver</server>
<type>ORACLE</type>
<access>JNDI</access>
<database>mydb</database>
<port>myport</port>
<username>myuser</username>
<password>mypassword</password>
<servername/>
<data_tablespace/>
<index_tablespace/>
<attributes>
  <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
  <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
  <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
  <attribute><code>PORT_NUMBER</code><attribute>myport</attribute></attribute>
  <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
  <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute>
  <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
</attributes>

- DennisH

Answer 3

添加

(FAILOVER_MODE=(type=select)(method=basic)(retries=20)(delay=3))))

将其定位到字符串的末尾对我们解决了问题。

- Adammmmm