Updating oracle sequence

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle.

Read full bio » I have six tables that all use one auto incrementing sequence in Oracle.

Problem is, every time the database is redeployed, the next value in the sequence is incorrect because it has to be set statically.

If we want the sequence to be used as a server-side default, meaning it takes place even if we emit INSERT commands to the table from the SQL command line, we can use the in both the Python-side and server-side default generation contexts ensures that the “primary key fetch” logic works in all cases.

Typically, sequence-enabled databases also support RETURNING for INSERT statements, which is used automatically by SQLAlchemy when emitting this statement.

It is most commonly used in conjunction with a single integer primary key column: Where above, the table “cartitems” is associated with a sequence named “cart_id_seq”.

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.This only occurs for primary key columns for an INSERT statement that is being asked to return this primary key value, where RETURNING or attribute is required.This is becaue the “post-fetch” operation requires that the primary key value already be available, so that the row can be selected on its primary key.For a server-generated primary key value, all databases provide special accessors or other techniques in order to acquire the “last inserted primary key” column of a table.These mechanisms aren’t affected by the presence of object, which is considered to be a special case of “column default”.So I found this sql on the internet which will update your sequence number according to the next unused ID from your table.

Tags: , ,