Hi @RolandGrund , that Oracle error message is not likely to be a bug with KNIME, although it could be caused means that somewhere where Oracle attempted to interpret your query it attempted to convert some non-numeric character data into a number.
For example, suppose you had the following table:
create table mytable (code varchar2(5), name varchar2(100));
insert into mytable(code, name) values ('1','Adam');
insert into mytable(code, name) values ('2','Bob');
The following select would work, because although code is a varchar2 column, the data contained within can be interpreted as numeric
select * from mytable where code=1;
However, if you added the following data row:
insert into mytable(code, name) values ('c','Charlie');
Your table would now look like this:
| code |
name |
| 1 |
Adam |
| 2 |
Bob |
| c |
Charlie |
The following query would now fail with
ORA-01722: invalid number
select * from mytable where code=1;
e.g. demo using Oracle Live SQL
But with additional “c” row:
This fails because in order to find rows where code=1, Oracle attempts to translate all values of code to numeric, and hits the “invalid number” error when it gets to “c”.
Now without knowing your actual table data, and the query, it is not possible to tell you exactly where your problem is, but the problem will be with your query or a misunderstanding of your data.
It could be that the query you are generating with KNIME needs to have single quotes put round a numeric value, but you’d need to give more information and show us the actual query. Unfortunately Oracle is not very forthcoming about the specific row of data causing the problem, or even the column which is frustrating. I’ve been there many times! 
The example query that you included appears to be slightly garbled, but did it contain a WHERE clause?
Also, did you tell SQL Developer to return ALL rows from the entire returned dataset or did you just have it return the first 50 (for example) rows? If it only returned a subset of the rows, it is possible that it didn’t hit the error in the returned data. KNIME will be returning ALL rows, so Oracle will hit the error when it gets to the offending row.
Which nodes are you using in KNIME to query the database, and have you told KNIME to convert any returned data to Integer or other numeric fields using the type mapping tabs on any of the db query nodes?
Somewhere, either intentionally or unintentionally, you are asking Oracle to convert character data to numeric data and it is failing in Oracle.
