PLSQL Create Sequence start with value automated

Question

Inquiring if there is a way to automatically set START WITH value of a sequence.

I want to drop and create the sequence since there is an error when inserting a row, problem is it affected all sequences hence we are unable to insert a row on multiple tables.

What do you mean by "automatically"? You may use identity column with [`start with limit`](https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#CJABEJEF) clause to allow Oracle to calculate current value based on the table data. — astentx, Oct 18 '22 at 07:35
As far as I remember the parameter of START WITH must be an INTEGER, not an expression, so if you want to set it automatically you have to calculate its value before and assemble the "CREATE SEQUENCE..." into a VARCHAR2 to then EXECUTE IMMEDIATE it in a PL/SQL block. — p3consulting, Oct 18 '22 at 07:40
_I want to drop and create the sequence since there is an error when inserting a row_ Perhaps re-creating the sequence is not the solution. Can you [edit] your question and post details of the _error when inserting a row_ ? — Abra, Oct 18 '22 at 07:49
Does [this](https://stackoverflow.com/questions/73982704/can-we-continue-a-column-with-values-using-sequence-in-sql) help ? — Koen Lostrie, Oct 18 '22 at 07:50
Hi all, Apologies since English is not my first language. What happened is I created a sequence with a starting value of 1522 and is conflicting with another row that has the ID of 1522, I want to re-create the (Drop and Create) the Sequence without checking on the table on what the latest value is for ID (I don't have access to the table and requesting to extract the data will take a long time and the fix will possibly not work if I put a static number in the "START WITH"). — Kyujiin, Oct 18 '22 at 08:32
How did you pick 1522 - and was the row with ID 1522 created manually afterwards? If you have a mix of inserts using the sequence and providing their own values then you'll continue to get clashes in the future - it's possible to handle it but it would be better to have a single mechanism that always uses the sequence, via a trigger or as an 'always' identity column. — Alex Poole, Oct 18 '22 at 10:11

score 0 · Answer 1 · answered Oct 18 '22 at 11:37

You can create small scripts like this when you create it :

-- Created on 18.10.2022 by ALI.FIDANLI 
declare 
  -- Local variables here
  i integer;
BEGIN
  select max(cb_refno)+1 into i from dwh.customer_fact ;
  dbms_output.put_line(i);
  
  execute immediate 
  ' create sequence SEQ_TEST minvalue 1 maxvalue 9999999999999999999999999999 start with ' 
  || i || 
  ' increment by 1 cache 20';
  
END;

PLSQL Create Sequence start with value automated

1 Answers1