• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1015
  • Last Modified:

Oracle CREATE TABLE Statement: Missing Right Parenthesis

(Warning:  SQL expert, Oracle N00b)

This code executed in DbVisualizer results in an 'Missing Right Parenthesis' error (below image).  In the editor the red squiggly line is below the word DEFAULT in the schdnum int line.   There may be some follow-on issues preventing the CREATE TABLE from executing.
CREATE TABLE aimstotrax
(
schdnum int NOT NULL DEFAULT 0,
flightdate date NOT NULL,
flightnum char(6) NOT NULL,
airlinecode char(2) NOT NULL DEFAULT 'SY',
departairportcode char(3) NOT NULL,
arriveairportcode char(3) NOT NULL,
acnum char(6),
out_utms datetime,
off_utms datetime,
in_utms datetime,
on_utms datetime,
cycles int NOT NULL DEFAULT 1,
flightlog char(20) NOT NULL,
ataindicator char(1) NOT NULL DEFAULT 'N',
legsequenceid int NOT NULL DEFAULT 1,
deleteindicator char(1) DEFAULT 'N',
traxscheduledindicator char(1) NOT NULL DEFAULT 'N',
traxactualindicator char(1) NOT NULL DEFAULT 'N',
traxdelayindicator char(1) NOT NULL DEFAULT 'N',
createduserid varchar(20),
createdtimestamp datetime,
lastupdatedby varchar(20),
lastupdatedtimestamp datetime
)
GO

ALTER TABLE aimstotrax ADD CONSTRAINT uq_attflt UNIQUE(flightdate,flightnum,departairportcode);
GO

COMMENT ON TABLE aimstotrax IS 'Target table for SSIS package ssis_aims_to_trax'
GO

Open in new window

Returns this error messageMissing Right Parenthesis
0
Jim Horn
Asked:
Jim Horn
  • 2
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
No GO in Oracle.  It's a semi-colon to end the command.
...
lastupdatedtimestamp datetime
);


Now reverse the not null:
schdnum int DEFAULT 0 NOT NULL,
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Bingo bango.  Thanks bro.

Moved all DEFAULT x before NOT NULL
Changed datetime to char(4).  My file has MMSS and I have to convert anyways.
Chnaged GO to semi-colons.
0
 
sdstuberCommented:
also, Oracle doesn't have a datetime type,  it's just DATE or TIMESTAMP

DATE has second-level resolution

TIMESTAMP can go to nanosecond resolution

TIMESTAMP WITH TIME ZONE, as the name implies can also store time zone information

Usually people just use DATE type though
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
another thing,  CHAR is not generally used in Oracle other than CHAR(1)

for variable length strings,  VARCHAR2  is the preferred type.

CHAR will right pad all of the data

i.e.   insert 'X' into CHAR(5)  you'll get 'X    '   out of it
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.

I might suggest you get in the habit of naming your constraints?

If you do not provide a name, Oracle makes one up for you.  If you provide a meaningful name it makes things easier to find or if you come across a constraint violation error someday Oracle should provide the name and hopefully you'll know exactly what the problem is.

There are a few ways to name them.

Here is one:
drop table q purge;
create table q(
      col1 int default 0 constraint col1_not_null not null,
      col2 char(1)
);
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks for the extra info.  I've already awarded points, so I'll catch you on another question.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now