SQL command not properly ended error : Error: ORA-00933

Question

Here is the query i am trying to execute..

CREATE SEQUENCE "GARY"."TABL_PROD_DWH_SEQ" 
MINVALUE 1 MAXVALUE 99999999999999999999999999999999
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOPARTITION;

Error starting at line : 1 in command -

CREATE SEQUENCE "GARY"."TABL_PROD_DWH_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOPARTITION;

Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" 'Cause: 'Action:

Oracle docs say the maxvalue can have 28 or fewer digits for positive values , but you have 32 digits set. — SuleymanSah, Nov 03 '19 at 14:34

score 1 · Answer 1 · answered Nov 03 '19 at 14:28

1

What version of Oracle are you using? NOPARTITION doesn't ring a bell. It might only be available on more recent versions.

You can also use NOMAXVALUE. That seems more intuitive than your arbitrary value.

answered Nov 03 '19 at 14:28

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Nov 03 '19 at 14:30

Explanaton

Please see the following question/answer: Oracle 12.2 - Replacement of NOPARTITION feature

Your problem is NOPARTITION.

It is a non-documented unsupported feature after a version of Oracle (I think 12).

Also see: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523071800346490539

Test

http://sqlfiddle.com/#!4/1afa88 (Oracle 11g R2)

The below does work:

CREATE SEQUENCE "TABL_PROD_DWH_SEQ" 
MINVALUE 1 MAXVALUE 99999999999999999999999999999999
INCREMENT BY 1 START WITH 21 CACHE 20
NOORDER NOCYCLE
;

score 0 · Answer 3 · answered Nov 03 '19 at 16:29

You've already been told most of the things. Let me add a few more words.

Your problem looks like the one that happens when people use parameters they don't quite understand. I apologize if I sound impolite, but - that's my impression. Why? Because of the look of that CREATE SEQUENCE statement.

No living person I know has ever manually written it

using double quotes around uppercase owner and sequence name,
nor have they used such a MAXVALUE (heck, it is indefinite anyway),
nor they want to explicitly remind Oracle that default increment is 1,
as well as cache (which is 20 by default)
not to mention other options that are default anyway

Apart from start with, everything else is default anyway so you could have used

SQL> create sequence tabl_prod_dwh_seq start with 21;

Sequence created.

SQL> select tabl_prod_dwh_seq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select tabl_prod_dwh_seq.nextval from dual;

   NEXTVAL
----------
        22

SQL>

and get the same result. So ... have a look at documentation related to your database version, try to follow it, don't do what's not allowed (or undocumented), keep it as simple as possible and you'll be good.

SQL command not properly ended error : Error: ORA-00933

3 Answers3

Explanaton

Test