Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
tale103108
Contributor III
Contributor III
‎2022-06-21 03:25 PM

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

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.

0695b00000Sq9XZAAZ.png

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

Labels (3)
Labels
1,286 Views
0 Likes
1 Solution

Accepted Solutions
mq123
Contributor II
Contributor II
‎2022-09-19 10:32 AM

In response to mq123

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

 

 

 

 

 

 

 

View solution in original post

1,286 Views
0 Likes
11 Replies
Anonymous
Not applicable
‎2022-06-22 12:39 AM

Hi

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

 

Regards

Shong

975 Views
0 Likes
tale103108
Contributor III
Contributor III
‎2022-06-22 11:21 AM
Author

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

 

0695b00000SqF7IAAV.png 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.

975 Views
0 Likes
tale103108
Contributor III
Contributor III
‎2022-06-22 01:12 PM
Author

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

 

0695b00000SqG7OAAV.pngOne '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?

975 Views
0 Likes
Anonymous
Not applicable
‎2022-06-23 02:14 AM

In response to tale103108

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

 

975 Views
0 Likes
Anonymous
Not applicable
‎2022-06-23 02:58 AM

In response to tale103108

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

0695b00000SqK3QAAV.png

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

0695b00000SqK3VAAV.png

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

Please try and let me know if it works.

 

Regards

Shong

 

 

975 Views
0 Likes
Anonymous
Not applicable
‎2022-06-27 05:52 AM

In response to Anonymous

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

975 Views
0 Likes
Anonymous
Not applicable
‎2022-06-28 06:07 AM

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

975 Views
0 Likes
mq123
Contributor II
Contributor II
‎2022-09-17 05:26 AM

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.

975 Views
0 Likes
Anonymous
Not applicable
‎2022-09-19 03:06 AM

In response to mq123

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

 

 

975 Views
0 Likes