DB Connector Oracle

[grzegorzZF]

Hello, I have a problem with connecting to the Oracle database, I have all the data regarding the port, etc. correctly configured, but I still have a bug like in the photo below.

[takbb]

Hi @grzegorzZF, and welcome to the forum!

I would recommend using the dedicated Oracle Connector rather than the DB Connector, but having said that, both should work, so it does look like you still have a config error somewhere.

Here is what a demo connection I have created locally looks like when using DB Connector

and the equivalent using the Oracle Connector node

I’ve just obfuscated my server name, but the remainder of the info here is purely for my demo db, so I’ve left intact so you can compare with the format you are using.

Some questions:

1. Which version of Oracle are you using?

The message you are getting typically indicates an issue with the port number (see my list of errors at the end), but it might indicate that an Oracle service isn’t running, or that the TNS Listener isn’t configured correctly… so…

2. Can you connect to the database from the same PC (or any other PC) using any other application, such as SQL Developer?

3. Is your Oracle database running on the same machine, or is it on a remote server?

4. Are you using a tnsnames.ora file to resolve the connection to the database? If so, it’s possible there is an error in it (although that’s unlikely if other applications can connect from the same pc.)

Perhaps you can show your config screen (with any sensitive information changed, but such that we can still see how the information is formatted)

For info, I tried various ways of “breaking” my connection, and below is the results. Of all the different “mistakes” when using the Oracle Connector, only incorrect port number seemed to produce the “could not establish the connection” error, although with the DB Connector, the “could not establish connection” error appears to occur if either the port, or the host server are incorrect, although I did get a few variable results

Entering wrong host name:
ERROR DB Connector 8:10 Execute failed: IO Error: Unknown host specified

Entering incorrect service/database name

ERROR DB Connector         8:10       Execute failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Entering incorrect parameter format
e.g. @servername:port:databasename
instead of @servername:port/databasename

ERROR DB Connector         8:10       Execute failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Incorrect Port Number or Incorrect host name along with invalid format - such as using “:” instead of “/” before database name
ERROR DB Connector 8:10 Execute failed: IO Error: The Network Adapter could not establish the connection

[grzegorzZF]

Hi, thank you for your answer and help. I have tried various methods without successfully doing so. There are no problems with connecting to this database using other programs. I work a lot in Power BI and I have no problem with connectivity there. Of course, after installing the appropriate driver. Maybe that’s the problem … The database is in a different location than my computer.

I am using this oracle driver you can see in the picture above. In general, the base to which the link looks like this:

host:10.214.6.:1521/bibpl*.ad.trw.com
DB ACC_VWE***
Port 1521
SID bibpl***

Of course, I cannot give you the password and the user, and I generally count on the confidentiality of the data that I have presented to you only because of your help in setting up the connection.

Thank you

[bruno29a]

Hi @grzegorzZF , from your screenshot, you seem to be setting up your host in the template.

You don’t have to touch the URL template, leave it as it is.

Once you add the driver, you then can add the host name in the configuration window. You should end up with the same UI as what @takbb has:

That is where you need to add your hostname and credentials

[takbb]

@grzegorzZF

host:10.214.6.**:1521/bibpl***.ad.trw.com
DB ACC_VWE***
Port 1521
SID bibpl***

Hi @grzegorzZF, in addition to @bruno29a’s note about entering the config in the correct place, that also looks like a rather odd host name! You appear to have a combination of two host names and a port number and database name thrown together there and concatenated with a “.”.
Are you sure that’s correct? I can see why jdbc might get confused if you had host names containing “:” and “/” symbols!

Also is the bibpl… part definitely a SID and not a service name? If it’s actually a service_name it should be preceded by a “/” rather than a “:”.

[ipazin]

Hello there,

adding link from KNIME Database Extension guide with example on how to connect to Oracle DB:
https://docs.knime.com/latest/db_extension_guide/index.html#connecting_examples

Welcome to KNIME Community @grzegorzZF and hope you’ll manage to connect!

Br,
Ivan