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

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

tale103108 — Fri, 15 Nov 2024 22:47:56 GMT

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 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 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

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

Anonymous — Wed, 22 Jun 2022 04:39:34 GMT

It seems the error occurs on c:\\, have a try to change it to c:/Oracle_ADB.

Regards

Shong

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

tale103108 — Wed, 22 Jun 2022 15:21:18 GMT

Seems better (I get connected) but now I get:

[statistics] connected

Exception in component tDBInput_1 (Test_ADB_Connectivity)

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

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 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: The Network Adapter could not establish the connection

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:571)

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

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

Caused by: oracle.net.ns.NetException: Unable to initialize ssl context.

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:346)

at oracle.net.nt.CustomSSLSocketFactory.getSSLContext(CustomSSLSocketFactory.java:307)

at oracle.net.nt.CustomSSLSocketFactory.getSSLSocketEngine(CustomSSLSocketFactory.java:273)

at oracle.net.nt.TcpsNTAdapter.connect(TcpsNTAdapter.java:174)

at oracle.net.nt.ConnOption.connect(ConnOption.java:174)

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:510)

... 14 more

Caused by: oracle.net.ns.NetException: Unable to initialize the key store.

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:619)

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:324)

... 19 more

Caused by: java.security.KeyStoreException: SSO not found

at java.security.KeyStore.getInstance(Unknown Source)

at oracle.net.nt.CustomSSLSocketFactory.getKeyStoreInstance(CustomSSLSocketFactory.java:767)

at oracle.net.nt.CustomSSLSocketFactory.loadKeyStore(CustomSSLSocketFactory.java:901)

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:610)

... 20 more

Caused by: java.security.NoSuchAlgorithmException: SSO KeyStore not available

at sun.security.jca.GetInstance.getInstance(Unknown Source)

at java.security.Security.getImpl(Unknown Source)

... 24 more

[statistics] disconnected

Job Test_ADB_Connectivity ended at 10:18 22/06/2022. [Exit code = 1]

Could it be the Additional parameters? I thought for Oracle the trusted store and key store were JKS? I tried changing SSO to JKS but no difference.

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

tale103108 — Wed, 22 Jun 2022 17:12:18 GMT

Talend now connects and runs against ADB. Needed to load the following oracle-specific jars to Talend:

oraclepki.jar

osdt_cert.jar

osdt_core.jar

ojdbc8-19.3.0.0.jar

One 'gotcha' is that I get 4 rows of 'noise' (meaningless data) before I get any valid data. (See highlighted data in yellow above).

Also, how do I load the libraries mentioned above permanently so I don't have to use tLibraryLoad component every time I build and run a job?

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

Anonymous — Thu, 23 Jun 2022 06:14:06 GMT

Hello,

Are you able to download and install these jar files using the Modules view?

Here is online document about: TalendHelpCenter: Installing external modules manually using the Modules view

And a KB article about: https://community.talend.com/s/article/Resolving-class-and-JAR-related-issues

Best regards

Sabrina

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

Anonymous — Thu, 23 Jun 2022 06:58:18 GMT

@tony esposito , try this way

Create a user routine.
Right click the routine and select 'Edit Routine libraries' option from pop-up tab to add these libraries.

Right click your job and select 'Set codes dependencies' option, and then select the user routine you created.

In the job, don't use tLibraryLoad component to these jars.

Please try and let me know if it works.

Regards

Shong

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

Anonymous — Mon, 27 Jun 2022 09:52:24 GMT

Hello,

It seems that the Use SSL checkbox is missing from Oracle 18 and is only present for oracle 12. (That checkbox loads these additional libraries.)

There's a jira tracking this: https://jira.talendforge.org/browse/TDI-46913

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

Anonymous — Tue, 28 Jun 2022 10:07:59 GMT

Hello,

Our R&D team are working at this issue

So far, you have to add 3 missing jars with 3 tLibraryLoad as a workaround.

Feel free to post your issue here.

Best regards

Sabrina

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

mq123 — Sat, 17 Sep 2022 09:26:23 GMT

Does anyone actually have a step by step guide to successfully connecting to Oracle ADB ( I'm using 21c) from Talend Open Studio. I've tried adding tlibraryloads and tkeystore etc and am still no further forward

I'm finding it hard to believe that a product whose whole existence is based on being able to connect to anything, hasn't been able to connect to Oracle ADB out of the box for several years.

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

Anonymous — Mon, 19 Sep 2022 07:06:23 GMT

Hello,

Could you please clarify in which Talend open studio version/edition you are?

Do you have SSL support issue as well while connecting to Oracle ADB 21C?

Here is online documentation about: TalendHelpCenter:

Supported Third-Party System/Database/Business Application Versions

In the documentation we provide a list of databases that are supported, in the sense that we do provide an SLA and technical support for them. This doesn't mean other (non-listed) databases will not work but simply we won't necessarily be equipped to help you with any issue you may face with them.

Best regards

Sabrina

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

mq123 — Mon, 19 Sep 2022 14:32:16 GMT

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 (/).

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

Anonymous — Tue, 20 Sep 2022 03:03:56 GMT

Hello,

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

Best regards

Sabrina