Updating an oracle sequence nextval
If the statement , or as SQL expressions which are rendered directly within an INSERT or UPDATE statement emitted by SQLAlchemy; they may also be client-side Python functions or constant values which are invoked by SQLAlchemy before data is passed to the database.
The typical use case for this context with regards to default generation is to have access to the other values being inserted or updated on the row.
I'm using Kettle 3.1 and i have a simple insert/update transformation with a table input, three lookups and an insert/update step. No rows are inserted but i the sequence has a new value (currvalue 1) so the sequence.nextval has been executed.
The insert/update step uses a unique key field to insert or update the data. TABLE1 REFERENCING NEW AS New OLD AS Old FOR EACH ROW BEGIN IF : NEW. If i execute the transformation again then everything runs perfect.
When INSERT statements take place for “cartitems”, and no value is passed for the “cart_id” column, the “cart_id_seq” sequence will be used to generate a value.
Typically, the sequence function is embedded in the INSERT statement, which is combined with RETURNING so that the newly generated value can be returned to the Python code: is subject to CREATE / DROP.