Issues with "Oracle Connector " and "DB Query Reader "

I’m new from here. Just beginning to use Knime Analytics. But very exited!
I’m used to do BI with Oracle’s Discoverer and recently with Tableau. Oracle became a very stressed set of tools/soft to be installed in order you can reach to BI environment to work. Tableau, does not have a free version just a trial one. So, a friend told me about Knime and I’m doing a try on it.

So, let me ask for a little help.
I’ have a “home” server running Linux+Oracle19 database. From another machine I’m be able to connect with server and data.
From Knime I’ve OJDBC8.JAR to connect to data and it seem working gracefully. But I’m experiencing some issues with "Oracle Connector " and "DB Query Reader "
Some time work, some times no!
Well, any tips are very welcomed!!! Thanks in advance!
Eduardo

Hi @EduardoRio and welcome to the KNIME Community. I use KNIME with Oracle but haven’t experienced intermittent problems like you appear to describe. Can you be a little more specific?

When you say sometimes it works and sometimes it doesn’t are you getting errors messages when it doesn’t work?

Is it the same queries that work one time but not another or is it that some queries work but different queries don’t?

2 Likes

Hi!
Thanks for your reply.
I’m not able to handle with it right now. I’ll take a look on it asap and get back here with more specific information.
Regards,
Eduardo

Hello,
Right now the “connector” are working.
“Querie Reader” instead shows a red cross and the massage "Execute failed invalid SQL: sqlkind = uninitialized
BTW, configuring the component, at the Setting clinking the "evaluate " button shows the columns and data from the table.

Thanks!

Hi @EduardoRio that’s not an error I’ve come across myself but looking at forums outside of knime, it looks like that error message tends to occur due to certain syntax errors, often from whitespace missing between some keywords.

Another common cause of errors in the DB Query Reader is the inclusion of a ; at the end of the select statement.

Semicolons should be omitted, because the query reader will wrap your select statement with an outer select, so if there is a ; at the end of your statement it will make the resulting sql invalid.

e.g if your select statement is

select sysdate from dual;

Knime will wrap it in the form

select * from (select sysdate from dual;)

At which point we can see this would generate an error as the ; isn’t allowed in a subquery!

A further possibility is that sometimes the oracle connection to the database gets closed by the database, but the connector node doesn’t see this.

If that happens you could try forcing a reconnection by resetting the db connector node and then executing it again. This should establish a new connection and might then allow your db query reader to work.

I don’t know if any of the above will assist with your problem but hopefully may be useful.

1 Like

Hi Brian!
Thanks for the responses! You’re right.
I’m just paying the price for the first steps on the way of being a little more bit familiar with the software. In that way, I’ve discovered the “Workflow Coach”, and it has been doing a great job!!!
So, let me say what is goal on this try on Knime.
I’m trying to create some kind of a cross table view to manipulate data from 4 data tables. A basic star model for information. 3 dimensions(like: tema=type; assunto=sub type; estate) and 1 fact table with the main and counting data (demanda).
This is the workflow I’m working in right now. I’m in trouble to chose the next component in order of get the view of a cross table…
firt workflow

Thanks again!
Eduardo

1 Like

you mean cross table like pivot table (Pivot Node in KNIME) ?
br

Could be! I’ll try this one!
Thanks!!!