Hi all,
I’m facing something strange when reading from Oracle database where when I extract in Knime, I get a column that is wrongly blank for which I expect values. I tried extracting in other applications such as SQL + and another ETL app and I get data for that column. Any idea?
Hi there!
can you share a bit more details like what node are you using, are you getting empty string cells or column with no cells, SQL statement…
Br,
Ivan
Hi, I am using a database reader and a simple
select column1,column2,column3 from table
and while we can clearly see that column2 has values in SQL +, (mix of values and nulls), the output from the database reader in Knime show a mix of nulls (? which is as expected) and where values are expected, we simply get a blank. column 2 in this case is a string.
Hi there!
That is weird. At least for me. Nothing special about that column for sure?
You can try maybe different set of nodes like Database Connector and after that Database Table Selector and after Database Connection Table Reader. And then in Database Table Selector you can also see result return for column2 without reading them into KNIME.
Also can you try new database integration nodes to see if the same behavior is there.
Br,
Ivan
Hi @ipazin,
I’m facing this issue and I’ve tried both ways, one directly uses Database Connector->Database Reader, and another with Database Connector->Database Table Selector->Database Column Filter->Database Connection Table Reader, but neither returns values where there should be.
I’m not sure if this is data type related, as all problematic columns are String, others that are datatime or number are good.
Hi @oliviaS!
Welcome to KNIME community. You also use Oracle database? The problematic column is of which type in a database?
Can you maybe try the new database integration nodes?
Br,
Ivan
Hi Ivan @ipazin,
Thanks for your reply.
Yes, it’s of Oracle type and could you give more ideas about database integration nodes?
This is really setting me back from using KNIME, difficult to debug since it’s not an error 
Cheers,
Olivia
I believe he was asking about the column’s data type (in addition to whether it was an Oracle database).
In addition to what datatype, what version of Oracle and what version of the JDBC drivers?
Exactly. JDBC driver version matters especially the old ojdbc14.jar is still seems to be used often and will lead to issues. Note that the 14 stand for java 1.4 = java 4 in current naming so extremely old and outdated.
Hi @oliviaS!
As @quaeler said I meant what column type in Oracle this data is stored in?
Also as suggested check your JDBC driver version.
Here is the link for the new database connector node which is currently in KNIME Labs. It is in extension that needs to be installled. On the right there is link how to install extensions if you haven’t done it before. I suggest trying these new nodes as they will replace the current ones.
https://hub.knime.com/knime/nodes/DB_Connector_Labs*0oD8015Fv-UmISSY
Feel free to ask if something is unclear and don’t give up on KNIME just yet 
Br,
Ivan
Thank you so much Ivan @ipazin
They’re of String type and I’m using ojdbc8.
Anyway, I’ll install the extension and try those new nodes first. May probably post further questions here 
Cheers,
Olivia
1 Like
Hi Olivia,
You are welcome. Feel free to post questions and please do get back to us with information did the new database nodes help with your issue.
Br,
Ivan
Hi @ipazin,
I’ve installed KNIME Labs extension and tried both DB Connector (Labs) and Oracle Connector (Labs); however, the result is still the same. Those columns of String type returns blank values.
Here is the query result from KNIME:
Below is the result of the same query from SQLDeveloper:
Only String type columns have such issues, any other approaches that I could troubleshoot this?
Regards,
Olivia
HI there @oliviaS!
Glad you haven’t gave up on KNIME. Let’s try to figure this out 
First, can you check what types are these columns in Oracle? (Like CHAR, VARCHAR, VARCHAR(2)…) - there is no String type in Oracle.
Second, is this only specific for one table or you experienced this on multiple tables?
Third, can you remove driver you have added (ojdbc8) and chose one KNIME has.
Br,
Ivan
1 Like
Also does the driver match with oracle version?
And what is the encoding of the database? Maybe it has a strange encoding leading to knime returning empty strings.
Hi oliviaS,
what is the version of the Oracle database and what Oracle data types are used for the columns in the table? To answer these questions you can execute this workflow.
What version of the SQLDeveloper do you use? Does the problem occur with several table or only this one?
Bye
Tobias
1 Like