I am using latest Knime on latest Win11, all updates installed.
I have a workflow in Knime reading a small table “LAND” from Oracle 11g and writing it into a new table “hs_output” in the same DB. Destination table is created new in DB.
I am using latest DB driver from Knime.
In Knime this workflow works perfect; reading from destination table “hs_output” runs well.
It could be a problem with grants, if you are connecting as a different user in SQLPLUS and TOAD to the one you are connecting with KNIME, but assuming you are using the same account, it is more likely the case-sensitivity of the table names.
Oracle, like most SQL databases actually has case-sensitive table names. If you don’t put double-quotes around the table name in your select statement, Oracle will assume you mean the upper case version, so
select * from hs_output
will be interpreted by Oracle as
select * from HS_OUTPUT
If in sys.all_tables, you see the tablename in lowercase hs_output you will need to double-quote the table name in your select statement
i.e. select * from "hs_output"
If you don’t want the pain of forever having to add double-quotes to your sql, you should convert your table’s column names to upper case in KNIME before creating the database table, or you could turn off the quoted identifiers, by selecting “delimit only identifiers with spaces” in the Oracle Connector:
This should cause KNIME to not place double-quotes around the column names when it creates them, causing Oracle to user the upper case variant. This is fine unless the column contains spaces, (I don’t recommend having spaces in db column names, but some people seem to like that kind of chaotic freedom ) as it will have the effect that columns with spaces in the names become case-sensitive whilst all other columns are treated as upper-case.
) as it will have the effect that columns with spaces in the names become case-sensitive whilst all other columns are treated as upper-case.