CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
J. Lee
J. Lee

Reputation: 13

'Missing right parenthesis' SQL DEVELOPER

was doing a school project but then came across this error when I was trying to create a table. 

CREATE TABLE SSV_PASSENGERS ( PASS_ID# CHAR(6),
 PASS_FNAME VARCHAR(15),
 PASS_LNAME VARCHAR(15),
 PASS_ADDRESS VARCHAR(30),
 PASS_NATION VARCHAR(20),
 PASS_DOB DATE(yyyy-mm-dd),
 PASS SATRATE VARCHAR(2),
 PASS_CAB# CHAR(4),
 PASS_CABFARE NUMBER(6,2),
 PASS_TOTALEX  NUMBER(7,2),
 PASS_MEDINFO VARCHAR(30),
 PASS_DIET VARCHAR(30),
 PASS_EMNAME VARCHAR(20),
 PASS_EMPHONE# CHAR(10),
 PASS_ALTID CHAR(5),
 CONSTRAINT SSV_PASSENGERS_PK PRIMARY KEY (PASS_ID#) )

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

Could anyone tell me what's wrong with the script?

Upvotes: 1

Views: 12653

Answers (3)

thatjeffsmith
thatjeffsmith

Reputation: 22412

I found a few problems.

  1. DATE (PASS_DOB DATE(yyyy-mm-dd) - you just need DATE
  2. PASS SATRATE VARCHAR(2) - you need an underscore
  3. VARCHAR vs VARCHAR2 - VARCHAR is there to support ANSI - always use VARCHAR2

VARCHAR Datatype The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings. (Docs)

The working code:

create table SSV_PASSENGERS (
    PASS_ID# char(6),
    PASS_FNAME varchar2(15),
    PASS_LNAME varchar2(15),
    PASS_ADDRESS varchar2(30),
    PASS_NATION varchar2(20),
    PASS_DOB date,
    PASS_SATRATE varchar(2),
    PASS_CAB# char(4),
    PASS_CABFARE number(6,2),
    PASS_TOTALEX number(7,2),
    PASS_MEDINFO varchar(30),
    PASS_DIET varchar(30),
    PASS_EMNAME varchar(20),
    PASS_EMPHONE# char(10),
    PASS_ALTID char(5),
    constraint SSV_PASSENGERS_PK primary key ( PASS_ID# )
);

And since you're using and tagged SQL Developer, we try to show you where your problems are, look for the pink squiggles near the offending bad SQL.

enter image description here

Upvotes: 0

APC
APC

Reputation: 146189

The error message ORA-00907: missing right parenthesis indicates a syntax error. Sometimes it means we do actually have a unpaired left bracket. But often it means the compiler has come across an unexpected character, which might be a hanging comma or an unexpected identifier, that the compiler interprets as an attempt to start a new statement without properly closing the current CREATE TABLE statement.

This is not intuitive. However, now you know what the error message really means, the next time you get it you should think to yourself: ah, I have made a typo in my code, I must pore over it until I find my bloomer.

In this case I think the problem is this: PASS_DOB DATE(yyyy-mm-dd).

Oracle stores dates in a standard structure: format masks are just for displaying as or casting from strings.

The way to declare DATE columns is simply: PASS_DOB DATE.

Also, you're missing an underscore from one of your column names: it should be PASS_SATRATE VARCHAR(2).

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562240

As far as I see, the parentheses in that statement are balanced. Is it possible the error is reported regarding some different statement?

Oracle allows # as part of column identifiers, but discourages it.

Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration: Schema Object Names and Qualifiers says:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

(emphasis mine)

Interesting that the same paragraph in the Oracle 12c docs does not include the same warning.

Is it possible that you are executing that create table statement in some environment that treats # as a comment character (like shell or Ruby code) and ignores all the text following the #?

Upvotes: 0

Related Questions