Solved

Oracle CREATE TABLE Statement:  Missing Right Parenthesis

Posted on 2014-04-23
6
853 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 500 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 65

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 65

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
add more rows to hierarchy 3 35
Disable TLS1.0 on Win 2012 server 7 67
Change this SQL to get all nodes 3 40
Applying Roles in Common Scenarios 3 19
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

726 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