?
Solved

Oracle CREATE TABLE Statement:  Missing Right Parenthesis

Posted on 2014-04-23
6
Medium Priority
?
914 Views
Last Modified: 2014-04-23
(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
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40017474
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
 
LVL 66

Author Closing Comment

by:Jim Horn
ID: 40017499
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40017501
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40017509
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40017527
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
 
LVL 66

Author Comment

by:Jim Horn
ID: 40017532
Thanks for the extra info.  I've already awarded points, so I'll catch you on another question.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question