2

I am trying to alter a sequence with the result of two subqueries. I am new with Oracle and stuck with a syntax. If you can, please help me with this :

alter sequence COM_UPDN_BASE_FRMT_DTL_SEQ increment by ( (select max(id) from COM_UPDN_BASE_FRMT_DTL) - (select last_number from all_sequences where sequence_name = 'COM_UPDN_BASE_FRMT_DTL_SEQ') ) ;

Upon Executing this, I am getting an "invalid number" error. What am I missing here?

Himanshu Arya
  • 75
  • 1
  • 9
  • 1
    The syntax requires an integer literal, not an expression. You might find [this](https://stackoverflow.com/q/51470/266304) useful. (Also, `last_number` doesn't mean quite what you think - [as seen here](https://stackoverflow.com/q/23631628/266304).) – Alex Poole Jul 10 '20 at 22:31

2 Answers2

1

You'll definitely want PL/SQL and dynamic SQL, but don't use LAST_NUMBER.

Try something like this instead:

DECLARE
    l_max NUMBER;
    l_curr_seq_val NUMBER;
    l_new_seq_val NUMBER;
    l_curr_inc_bv number;
BEGIN
    --Get max value from table
    SELECT max(id) INTO l_max FROM COM_UPDN_BASE_FRMT_DTL;
    --get current value from seq and current increment_by from dba_sequences
    SELECT com_updn_base_frmt_dtl_seq.nextval,increment_by INTO l_curr_seq_val,l_curr_inc_bv FROM dba_sequences WHERE sequence_name = 'COM_UPDN_BASE_FRMT_DTL_SEQ' and sequence_owner=USER;
    --If there is a difference, do the fix
    if (l_curr_inc_bv != l_max-l_curr_seq_val) then
        EXECUTE IMMEDIATE
        'ALTER SEQUENCE com_updn_base_frmt_dtl_seq INCREMENT BY ' || (l_max - l_curr_seq_val);
        SELECT com_updn_base_frmt_dtl_seq.nextval INTO l_new_seq_val from dual;
        EXECUTE IMMEDIATE
        'ALTER SEQUENCE com_updn_base_frmt_dtl_seq INCREMENT BY ' || (l_curr_inc_bv);
    end if;
END;
/
halfer
  • 19,824
  • 17
  • 99
  • 186
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
-1

You should try it with variables and dynamic sql:

DECLARE 
    l_max NUMBER(5,0);
    l_last NUMBER(5,0);
BEGIN
    SELECT max(id) INTO l_max FROM COM_UPDN_BASE_FRMT_DTL;
    SELECT last_number INTO l_last FROM all_sequences WHERE sequence_name = 'COM_UPDN_BASE_FRMT_DTL_SEQ';
    
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE com_updn_base_frmt_dtl_seq INCREMENT BY ' || (l_max - l_last);
END;
Luke
  • 127
  • 1
  • 6
  • This won't work. LAST_NUMBER does not work as you think it does, *particularly* if the CACHE is set to some value creater than 1. – Mark J. Bobak Jul 10 '20 at 22:33