Create Sequence automatically with selected VALUE

[willyant]

How can I use a specified number as the value in Start with parameter for CREATE SEQUENCE.
My Code :
DECLARE
My_session Number(5);
BEGIN
SELECT MAX(SessionID) INTO My_session FROM My_user;
END;
Then I used the next lines :

DROP SEQUENCE Myaw_SessionID;
CREATE SEQUENCE Myaw_SessionID
INCREMENT BY 1
START WITH &My_session
NOMAXVALUE
NOCYCLE
CACHE 20;

But it didn't work properly. Because i must manually insert the START WITH's Value.
Who knows, how can i automated it. THANKS A LOT

[akkerend]

Ampersand in SQL*PLus is used for substitution variable, and outside of PL/SQL block the variable my_session does not exist.

In 8i use EXECUTE IMMEDIATE to drop and recreate the sequence.

DECLARE
My_session Number(5);
BEGIN
SELECT MAX(SessionID) INTO My_session FROM My_user;
EXECUTE IMMEDIATE 'DROP SEQUENCE Myaw_SessionID';
EXECUTE IMEMDIATE 'CREATE SEQUENCE Myaw_SessionID START WITH '||to_char(my_session);
END;
/