CODEWITHSUNDEEP
sqloracle-databasesequence
dazed-and-confused
dazed-and-confused

Reputation: 1333

How to create a sequence with start with value that is a query

I saw this same question here Create a Sequence with START WITH from Query but it does not work. It has 12 up votes so I'm not sure why it's not working for me.

This is what I have:

declare
    l_new_seq INTEGER;
begin
   select max(expense_detailid) + 1
   into   l_new_seq
   from   expense_detail;

   execute immediate 'create sequence expense_detail_seq 
                      start with ' || l_new_seq || ' increment by 1';
end;
/

And this is the error I get:

ORA-06550: line 3, column 17:
PLS-00103: Encountered the symbol "create sequence expense_detail_seq start with " when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "create sequence expense_detail_seq start with " to continue. (DBD ERROR: error possibly near <*> indicator at char 27 in '
    BEGIN
      immediate <*>'create sequence expense_detail_seq start with ' || l_new_seq || ' increment by 1';
    END;
  ')

ORA-00900: invalid SQL statement

Any ideas? Thanks!

Upvotes: 0

Views: 319

Answers (1)

Mario Rossi
Mario Rossi

Reputation: 7799

Make all the text in a single line. Or at least each literal.

Also, what is the product/utility and product version ?

Upvotes: 1

Related Questions