I want to set the query result as minvalue

Question

Lets consider Result of this query may 22 Ie., (Select max(screen_id)+1 from table_screen) = 22

create sequence SEQ_TEST
minvalue SELECT MAX(SCREEN_ID)+1 FROM TABLE_SCREEN
start with 25
maxvalue 999999999999999999999999999
increment by 1
nocache;

How to set the result of a query to a minvalue in sequence??

Answer 1

You could use dynamic sql like this:

Edit: now tested code

--Test setup
create table table_screen ( screen_id INTEGER NOT NULL);
/
insert into table_screen values (1);
/
insert into table_screen values (10);
/
insert into table_screen values (21);

new block

DECLARE
    MinVal            INTEGER;
BEGIN
    SELECT MAX(SCREEN_ID)+1 INTO MinVal FROM table_screen;

    EXECUTE IMMEDIATE 'CREATE SEQUENCE seq_test START WITH ' || MinVal;
END;
/

I removed the extra parameters, just to keep it small.

Also note: that MINVALUE is used when increment is negative, according to my favourite Oracle help guy so I swap the code to create with START WITH instead as you had a positive increment.