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

Question

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!

What you say you're doing is fine, but the error message you've shown indicates that you're actually running something different. The `execute` keyword is missing (which gives this error) and you have an extra begin/end wrapper. I'm wondering if your client (I'm guessing this is a Perl script from the DBD reference?) is replacing `execute` with `exec`, and is maybe running separate statements. The quoted line number is confusing though. You'd get exactly this if you did `exec immediate ...` from a SQL*Plus prompt, except it would say line 1. If it's in a script, can you show the context? — Alex Poole, Aug 28 '13 at 19:20
Works for me: http://sqlfiddle.com/#!4/4d6f7/1 Which tool do you use to run the statement? Maybe it does not recognize the `/` delimiter? — , Aug 28 '13 at 19:20

Mario Rossi · Accepted Answer · 2013-08-28T18:33:15.383

1

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

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

edited Aug 28 '13 at 18:33

answered Aug 28 '13 at 18:26

Mario Rossi

7,651
27
37

Begin and execute are each on their own full lines, no breaks (I added them here just for display). As for version: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production – dazed-and-confused Aug 28 '13 at 18:37
1

Unless it's confusing the client, it isn't usually necessary for it all to be on one line; it would be with `exec` from SQL*Plus (though you can use continuation characters even then), but not with `execute immediate`. A literal can split over a line here though. – Alex Poole Aug 28 '13 at 19:23
@dazed-and-confused It's extremely strange. It executes with no issue in my configuration. Can you type the text from scratch or otherwise remove control characters? It's the only I can think of. – Mario Rossi Aug 28 '13 at 19:25
Ok so after trying to type line by line, I noticed a program right away with my DECLARE. Apparently, the DECLARE doesn't like being on its own line. I thought I tried putting it together but I didn't. Once I got the DECLARE var_name INTEGER on one line, it worked... And this is why i'm dazed-and-confused. – dazed-and-confused Aug 28 '13 at 19:40
@dazed-and-confused The only **other** thing I can think of is creating a stored procedure and executing it. This **should** be working! – Mario Rossi Aug 28 '13 at 19:41
@dazed-and-confused I still think you had a control character. Of course now I think near the `declare`. – Mario Rossi Aug 28 '13 at 19:43

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

1 Answers1