Can connect to Oracle ADB 19 via Metadata but database connection fails when used in Talend job - any ideas?

[tale103108]

Using Talend Open Studio 7.3.1 on Windows Server 2019 Standard

Trying to connect to Oracle Cloud ADB 19.x

------------------------------------------------------------------------------

I can connect when I create a Metadata Db connection for Oracle.

But when using the Oracle Db connection the connection in the job fails giving the following stack trace:

Jun 21, 2022 2:09:48 PM oracle.net.resolver.EZConnectResolver parseExtendedProperties

SEVERE: Extended settings parsing failed.

java.lang.RuntimeException: Invalid character at 27 : \

at oracle.net.resolver.EZConnectResolver.parseExtendedProperties(EZConnectResolver.java:393)

at oracle.net.resolver.EZConnectResolver.parseExtendedSettings(EZConnectResolver.java:366)

at oracle.net.resolver.EZConnectResolver.parse(EZConnectResolver.java:171)

at oracle.net.resolver.EZConnectResolver.<init>(EZConnectResolver.java:130)

at oracle.net.resolver.EZConnectResolver.newInstance(EZConnectResolver.java:139)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:665)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.tDBInput_1Process(Test_ADB_Connectivity.java:494)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.runJobInTOS(Test_ADB_Connectivity.java:935)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.main(Test_ADB_Connectivity.java:784)

Exception in component tDBInput_1 (Test_ADB_Connectivity)

java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid" 

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:854)

at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)

at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.tDBInput_1Process(Test_ADB_Connectivity.java:494)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.runJobInTOS(Test_ADB_Connectivity.java:935)

at timsreveal.test_adb_connectivity_0_1.Test_ADB_Connectivity.main(Test_ADB_Connectivity.java:784)

Caused by: oracle.net.ns.NetException: Invalid connection string format, a valid format is: "host:port:sid" 

at oracle.net.resolver.AddrResolution.resolveSimple(AddrResolution.java:631)

at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:519)

at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:682)

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:309)

at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1596)

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588)

... 9 more

[mq123 · Accepted solution]

Finally got this working in a job I.m using Talend 8.0.1 and Oracle ADB 21c

a. Before connection component, add the following tLibraryLoad components

oraclepki.jar

osdt_core.jar

osdt_cert.jar

ojdbc11.jar

(all in directory containing odbc11-full.tar.gz downloaded from Oracle website at JDBC and UCP Downloads page (oracle.com))

For connection component

Connection type = Oracle custom

DB version = Oracle 18 and above

URL = "jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1522)(host=oracle_adb_hostname))(connect_data=(service_name=oracle_adb_servicename))(security=(my_wallet_directory=C:/wallet_directory)))"

For oracle_adb_hostname and oracle_adb_servicename, unzip the wallet zip file used as "Configuration File" by sqldeveloper into your wallet_directory and take appropriate values from the tnsnames.ora file. Note that you need the full path to your wallet_directory specified for the my_wallet_directory parameter and any Windows slashes (\) need to be specified as unix style (/).

[Anonymous]

Hello,

Glad you got it sorted and thanks for coming back to give your solution!

Best regards

Sabrina