I am using Hibernate as the ORM to interact with Oracle.
This question is more on the SQL side to emulate the seqHiLo ID generator
which we are using to generate the Primary key for tables, i.e ID column.
Now I want to add a new ID column to an already created table, and this table has data.
I have one procedure to add the ID column to a table:
For instance:
alter table tableName add(columnName NUMBER);
Then create sequence
CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;
and after it use UPDATE statement to insert values in column like this
UPDATE tableName SET columnName=seq_test_id.NEXTVAL
Here I plan to use hibernate_sequence, to be in sync with Hibernate's way of generating the ID's.
The question is:
UPDATE tableName SET columnName=hibernate_sequence.NEXTVAL, does generate the ID's sequentially.
However, I need ID's as per the seqHiLo algorithm.
Is there a way to achieve this in pure SQL?
In short, can we have a customized way to generate IDs to be used in the new column, rather than a sequential one,
as shown above?