Solved

Oracle CREATE TABLE Statement:  Missing Right Parenthesis

Posted on 2014-04-23
6
791 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 76

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 73

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 73

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 76

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now