Using a variable inside an anonymous block to ALTER an SQL SEQUENCE not working

Question

I'm trying to alter the max value for a sequence inside of a declare statement. Inside the declare, I'm querying a table to get the count to make that count be the maximum for the sequence. More generally, I'm trying to set the maxvalue to be the count of the rows in my table.

CREATE SEQUENCE counter
START WITH 1
INCREMENT BY 1
MINVALUE 1 
MAXVALUE 5
CYCLE 
CACHE 4; 

DECLARE
      new_max NUMBER(2);
BEGIN
      SELECT count(*) INTO new_max 
      FROM Sim_Price;

      execute immediate 'ALTER SEQUENCE counter
              MAXVALUE new_max';
END; 
/

I'm getting the error that new_max is an invalid number. I'm not sure how I can make the max value be the same as the count for my table if when I get the value from the table, it doesn't recognize it as a number. If I just put a number straight in like 6 instead of new_max, it works fine. It just won't recognize the variable. I'm using SQLPLUS.

Stilgar · Accepted Answer · 2019-11-26 17:21:34Z

2

Your treatment of variable new_max causes it to be interpreted as a string of 'new_max'. You would need to present the content of new_max as a value in the string you are using for your EXECUTE IMMEDIATE.

execute immediate 'ALTER SEQUENCE counter maxvalue ' || TO_CHAR(new_max);

answered Nov 26, 2019 at 17:21

Stilgar

4612 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a variable inside an anonymous block to ALTER an SQL SEQUENCE not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related