Hi
Please help me by providing a step by step approach for connection Knime to Oracle DB
I am an Finance professional only experience in finance. I able work by loading excel files to Knime where as not able to connect to Oracle DB.
Hi @sujesh.
please first read the KNIME Database Extension Guide.
This guide contain all releveant information for the setup of a oracle db connection.
In addition you need some information from your IT colleagues for the database you like to connect.
(host name, database name, userid, password)
BR
2 Likes
Thank you very much for the reply, I read KNIME Data base Extension guide , I face problem in downloading the correct oracle driver from the site. All other details are with me .I able to connect to Oracle DB through Power BI, now I would like connect through KNIME
Please help by providing the correct oracle JDBC Driver
Thank you
Oracle JDBC drivers can be found here: JDBC and UCP Downloads page
You’ll need to ask your IT staff which version is appropriate for your team. Once you know that, you’ll likely be grabbing one of the .jar files from that site (something like ojdbc11.jar).
2 Likes
Thank you for quick reply. I downloaded and registered DB it through KNIME . Still not able to connect.
Is there any node available know what are the data base connection available in the Computer.
I can see tables through MS Excel and access it. Microsoft Power BI also able to connect to these tables.
Can you be more specific about the trouble you are running into? Any error messages or screenshots you have would be helpful.
Per the documentation linked above, after you register the Oracle JDBC driver in KNIME, you should then use the DB Connector node to access your database, along with other nodes downstream (for example, the DB Table Selector) to start extracting data.
3 Likes
Thank you for the reply. The error message attached with this reply
Kindly advice the steps to be followed to connect correctly.
Thank you
That’s an error on the Oracle side that could be caused by a lot of things. If you google the error message ORA-12514, you’ll see lots of suggested fixes. The most common one seems to be either the service name or the ports are incorrectly specified. Maybe you just have a typo?
3 Likes
Hi @sujesh , as the error message is stating that the listener doesn’t know of the “service” requested, and then shows a garbled service name in the message, what config are you specifying?
As @ScottF says, this is an Oracle message that effectively means you have specified an invalid Oracle database service name. In your screenshot it looks like your service name has been through some kind of hashing algorithm, as it is normally just a few (maybe up to about 8 or so?) alphanumeric characters
It would be helpful if we could see your config for the DB connector. By all means modify the actual values you are using before posting a screenshot if they are sensitive, but at least if we could see indicative values that you are using, it might help us to assist.
Also, what version of Oracle are you on, and which jdbc driver is it using?
4 Likes
Thank you @takbb and @ScottF for the quick reply. Let me contact with the IT team and collect details of Oracle version using. I connect to other network through remote desktop. Once again thanks for the help and explanation.
Thank you @takbb and @ScottF , for the earlier replies and guidance.I am attaching screen shot of connection. Oracle version using is 11g
Hi @sujesh, unfortunately that config screen doesn’t give much away. I’m assuming you’ve installed and configured the jdbc driver for Oracle so can you show the settings for the jdbc driver that you have installed.
This should be visible by choosing “Preferences” from the “File” menu in KNIME, and then going to KNIME - Databases , and finally opening the config for the Oracle driver by selecting it and the clicking Edit…
e.g.
Could you also tell us if “mall” is the SID or the SERVICE_NAME for your database? This is important as the format of the jdbc URL can differ depending on which you are using.
2 Likes
Thank you for the reply @takbb , mall is the DB name, I able to connect to this DB through Microsoft Excel and Power BI
Please find the attached file for the details as you asked
Hi @sujesh, thank you for posting the jdbc driver config screen.
Oracle doesn’t have a “database name” as such, but what is does have is either what is known as a SID , or alternatively a SERVICE NAME.
So “mall” will either be a SID or a SERVICE. Which it is depends entirely on your particular installation. However, it is important to know which one it is when you try to connect using jdbc. The reason for this can be seen if you were to press the circled ? in the config screen that you posted. What it would show you is this:
If “mall” is a SERVICE, then the URL template that you have:
jdbc:oracle:thin:@<host>:<port>/<database> should work ok.
However if “mall” is a SID, then you need to change the URL template as follows:
jdbc:oracle:thin:@<host>:<port>:<database>
i.e. the “/” prior to <database> becomes a “:”
So my initial thought is that maybe “mall” is actually a SID which is why it isn’t working. Can you try editing that URL template and changing it to:
jdbc:oracle:thin:@<host>:<port>:<database>
then giving it a go again with connecting, and see if it makes any difference.
1 Like
Thank you very much @takbb , Your last reply really helped to under stand the errors incurred. Now I able connect to Data Base but error shows while selecting tables.
Please find the attached files for further refrence
Hi @sujesh,
I’m glad to hear that you’re making progress.
So I take from this (and to help anybody else who has a similar connection issue in future) that “mall” was the oracle “tns name”, and that you replaced it in your connection node with “server” which is the service name shown in your tnsnames.ora file, and that your jdbc URL was therefore already correct? Hopefully that’s a reasonable summary.
In terms of your new error… that error is often caused by the inadvertent addition of a “/” or maybe a “;” at the end of a SQL statement (when code is copied/pasted from, for example, SQL Developer), but I’m puzzled at how this is occurring in a DB Table Selector, and in my experimenting I couldn’t make the Table Selector fail with that specific error message.
One thought… have you tried connecting using the Oracle Connector node rather than the generic DB Connector. Maybe there’s something odd about the settings on the DB Connector
Can you share screenshots of the config for the DB Table Selector?
Also, maybe there’s something on the “Advanced” tab of the DB Connector node configuration, so please share a screenshot of that too…
I’m going to be away from my KNIME pc for a week or so now, so I’ll do my best to assist via the forum on my phone, but I’m probably going to be a little blind… so hopefully somebody else will have ideas if I am unable to help
1 Like
Thank you @takbb @ScottF for support and guidance.
I able to connect now, I created connection through oracle instead off selecting default.
It is working and able to view data in tables.
Thank you for support
Here in Kerala , India, today 20-08-2021, festival Onam starts .
if time permits, please share data analytical workflows available for reference purpose.
2 Likes
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.