I’m trying to insert some data through the DB Insert node.
One of the data I want to insert is Double because doesn’t want me to cast it into an Integer since it’s an 18 digits number.
When I launch the insertion of this data to a numeric field of my database I have this error:
ERROR DB Insert 0:11036 Execute failed: Error while adding rows #-1000 - #0, reason: ORA-01461: une valeur ‘LONG’ ne peut être liée que dans une colonne de type ‘LONG’
Is there a mean to avoid the correction into long type or at least insert data through custom request so I would be able to cast my data in Oracle directly ?
Hi,
Since a relational database stores the data in a very structured fashion, all values in a column must have the same type. So if you want to store a very long value, the whole column must support long values. It is not possible to mix types.
Kind regards
Alexander
Hi,
if you have a 18 digits number, you cannot put it into a table column for integers, as integers generally only allow numbers up to 2147483647 (4 bytes). If you want to store that number, you have to change the column type to long, which allows numbers up to 9223372036854775807 (8 bytes).
Kind regards
Alexander
Ok but I have some other problem after that. The first one is that I have some numbers larger than
the long type authorize, but if I but them in Double value I have a systematic rounding of everything which is a bigger issue to me
Regards
Sébastien
Hi,
double also only has 8 bytes, so it cannot store more. Are your numbers all positive? You may be able to use an unsigned data type to double your range in the positive numbers. Which database system are you using?
Kind regards
Alexander
I don’t have any negative number, all of them are positive…
They are numbers calculated by another system I want to insert in an Oracle database and all of them have between 18 and 20 numbers
Hi,
I don’t know that much about Oracle databases and their types, but maybe you should think about storing the numbers as CHAR (20) instead.
Kind regards
Alexander
It was my last resort option
But I think I will do this.
Moreover, is there a logical reason why after converting a column into a Double it’s rounded ? It looks like Knime just want to keep the visible numbers ok and keeps the exponent to complete
I’m not sure if there is any difference between number and numeric but numeric will for sure work as else many of my workflows would not work. Number/numeric however need to be defined correctly so that your value fits into the field. If it’s defined as numeric(10,0) you will not be able to insert your 20 digit numbers.
I’ve been inserting numeric data with knime for close to a decade without issue so you are most likely doing something wrong on your side (wrong driver version, faulty workflow, faulty type mapping etc…)
Yes it’s why I posted on the forum I’m sure there is a thing I’m not doing well or not understanding well.
Is there a node where I can write my sql request by muself to see it’s the node I’m not using well or before ?
