11

In Oracle 10i, I'm running the following command:

ALTER TABLE jnrvwchnglst ADD
     ( jnrvwchnglst_userid NUMBER(10) NOT NULL DEFAULT 1 )

Yes jnrvwchnglst is an existing table and no jnrvwchnglst_userid is not an existing column.

The Oracle error message is:

ORA-00907: missing right parenthesis

What's wrong with this query and why does Oracle think I'm missing a parenthesis?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jason Cohen
  • 81,399
  • 26
  • 107
  • 114
  • Not that it's my business, but what on Earth do you keep in table with such a name? Junior Volkswagens Change List? :) – Quassnoi Apr 01 '09 at 17:13
  • 1
    @Quassnoi - The ORM system removes vowels from tokens because Oracle has its (unreasonable) 30-char maximum. So really that's "JoinReviewChangelist." – Jason Cohen Apr 12 '09 at 19:32

3 Answers3

21
ALTER TABLE jnrvwchnglst ADD
     ( jnrvwchnglst_userid NUMBER(10) DEFAULT 1  NOT NULL )
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
4

"(NOT) NULL" must be the last statement in the "ALTER" syntactically when present, so when Oracle saw that - and that the next char (your "DEFAULT" stmt) wasn't the expected terminating right ")", threw the error.

galaxis
  • 925
  • 8
  • 10
0

I have had this issue before where you can't add a column AND set the default/constraints in the same statement. The 'missing right parenthesis' is a red-herring. Oracle loves to use that error for cases that are unrelated to parenthesis (My guess is that their parsing logic falls through to 00907).

Try

ALTER TABLE jnrvwchnglst ADD ( nrvwchnglst_userid NUMBER(10) );
ALTER TABLE jnrvwchnglst ALTER ( nrvwchnglst_userid  SET DEFAULT 1 );
UPDATE jnrvwchnglst SET nrvwchnglst_userid = 1 WHERE nrvwchnglst_userid IS NULL;
ALTER TABLE jnrvwchnglst  ALTER ( nrvwchnglst_userid  SET NOT NULL );
JasonRShaver
  • 4,344
  • 3
  • 32
  • 39