Knime 2.8.2, Oracle.
I'd like to add a id column to my data table, populated from a database sequence. This would be followed by an insert into the appropriate table.
How does one select from a database sequence in Knime? It appears that Database Looping and Database Connector nodes both return the warning "ORA-02287: sequence number not allowed here" when I try to add a sql statement like "SELECT chembio.table_seq.nextval FROM dual".
We are trying to avoid adding a database trigger to the table to autopopulate the ID field on insert.
PS: obviously one could do something with a Java or R node, but expect there is something more "knime-ish"
I belief the problem arises from the sub-queries KNIME uses in most of the DB nodes since sequences are not allowed in sub-queries as described in the Oracle FAQ. The FAQ also state a workaround by defining a simple helper function which returns the next value of the sequence.
However in the Database Reader node which, as far as I know, does not uses sub-queries your statement should work.
Also, in KNIME 2.9 (I am not sure when it appeared, it is not yet listed in its web page) in the modular data genaration labs project there is a Counter Generation node. I guess that might be a good option for that too, it is KNIMEish, but maybe it is not applicable in this case.
Thank you for the quick reply.
The helper function trick from the ORAFAQ site worked. I filled the data table with values from the sequence with the "Database Looping" widget.
Database Reader did not work with a straight sequence - it seems to have the same subquery limitations as Database Looping and Database Connector.
Any chance you could post the node where you entered the helper function (actual example), so I can use it as reference to create my own? I don't know what "[other fields]" are; i.e., I don't know how to implement this code "WITH org as (
select org_id, org_id_parent, [other fields], package.function_to_get_next_ou_id ou_id
) select org_id, org_id_parent, ..., ou_id, prior ou_id ou_id_parent
connect by prior org_id = org_id_parent
start with org_id_parent IS NULL;"