using database sequences in Knime

KNIME Analytics Platform
1

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.

2

PS: obviously one could do something with a Java or R node, but expect there is something more "knime-ish"

3

Hi,

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.

Bye,

Tobias

4

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.

5

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.

6

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
     from table@dblink
) select org_id, org_id_parent, ..., ou_id, prior ou_id ou_id_parent
    from org
 connect by prior org_id = org_id_parent
   start with org_id_parent IS NULL;"

Thanks!