I use a sequence to number lines. From time to time, the sequence must be reinitialized in order to be counted from another value. I try this:

DECLARE V_TEMP_NUM number(9) :=0; BEGIN V_TEMP_NUM := 15 - SEQ.CURRVAL; ALTER SEQUENCE SEQ INCREMENT BY V_TEMP_NUM; SELECT SEQ.NEXTVAL INTO V_TEMP_NUM FROM DUAL; ALTER SEQUENCE SEQ INCREMENT BY 1; END;

But swears on ALTER . How to be? Generally swears at any ALTER, DROP or CREATE commands within a PLSQL block. How to execute these commands inside the block?

  • XY problem . Why do you need to number the tables? And the column numbers in the COLUMN_ID field of the COLUMN_ID system table will not work for you? - Dmitriy
  • @Dmitry, guilty, thought about his. I number the lines, of course. Simply they contain infa about columns of tables that will be dynamically created later. And so, of course, ID are distributed to lines. - Vitaly Jandulov
  • Honestly, this is even less clear. DDL commands in PL / SQL can only be executed through execute immediate , but when you make so many dynamic queries (changing the sequences, and then you still have the dynamic creation of columns planned), most likely you are doing something wrong. - Dmitriy
  • @Dmitry ignore why I need this table in the future, it is not essential, I removed it from the question. The point is very simple: I take the values ​​from the sequence and from time to time I need to give it a specific value. - Vitaly Jandulov
  • one
    @ 0xdb I just need to roll some data profile with inserts. In general, it seems to be handled. Created a sequence up to the plsql block, and within the sequence, threw off the execute immediate before filling each new table (changing the step to a negative one and stepping once, then returning the usual step). Now one script rolls the entire profile of the tables, and I don’t need a hardcode ID, indicating only formulas. - Vitaly Jandulov

1 answer 1

If we went to such a booze)))

 set serveroutput on; declare v_int integer := 200; v_ddl varchar2(512) := 'CREATE SEQUENCE sequence_2 START WITH '||v_int||' INCREMENT BY 1'; begin execute immediate 'drop sequence sequence_2'; dbms_output.put_line(v_ddl); execute immediate (v_ddl); end;