Database Writer fails for unknown type (DataValue) for number columns in oracle

KNIME Analytics Platform
1

I'm creating a workflow for uploading data. In certain cases ti can be that the file to be uploaded (excel) can contain an empty column (no values). If this column is a number column in oracle database the database writer will fail. It has the correct type set under SQL Types tab.

Due to this a tedious workaround is required with the column rename node and String to Number node is neccesary to make it work. You need to create a test file with empty values and only then can you configure the nodes to work in that scenario.

The problem I have with this that a) it works without issue for string columns and b) that these DataValue columns only contain nulls so it should be possible to have the Database writer insert null, regardless of type. Maybe add a check box "Insert DataValue columns as null". This should be easy to implement but a better would be that the Node those this automatically.

2

Can you provide more details? What type does the column in KNIME have, what's the error message and what is the type mapping defined in the database writer? What do you mean with "no values" in your empty column? Are they empty strings or missing values?

3

They are missing values and becasue the whole column in the excel file has no value the datatype in KNIME is ? (or DataValue). Database writer is not abel to write such a column to an oracle number field but it works for varchar2 fields.

4

I see. This is a corner case that will be fixed in 2.11. Until then you can work around this problem by first using the "Column Rename" node and set the type of the "?" column to StringValue. Then use the "Number to String" node to create an integer (or double) column with only missing values. Now the Database Writer should be happy.

5