PGPP (was: ORA-00905: missing keyword Need advice)

FutureZone wrote:
> Hi all,
>
> I've got this error when I create a table called "Offering" in
> SQL*Plus as follow:
>
> CREATE TABLE Offering( OfferNo INTEGER NOT NULL,
> CourseNo CHAR(6) NOT NULL,
> OffLocation VARCHAR(50),
> OffDays CHAR(6),
> OffTerm CHAR(6) NOT NULL,
> OffYear INTEGER NOT NULL,
> FacSSN CHAR(11),
> OffTime DATE,
> CONSTRAINT PKOffering PRIMARY KEY(OfferNo),
> CONSTRAINT FKCourseNo FOREIGN KEY(CourseNo) REFERENCES
> Course ON DELETE NO ACTION
> ON UPDATE NO ACTION,CONSTRAINT FKFacSSN FOREIGN
> KEY(FacSSN) REFERENCES Faculty
> ON DELETE SET NULL ON UPDATE CASCADE)
> /
>

This is not really Oracle, is it? It's Sybase, SQL Server or MySQL. The following things should be different, for reasons varying from "Not recommended by Oracle" to "PGPP" (or Pretty Good Programming Practice):

Integer datatype; Oracle uses number

Columns with Number as name should be numbers; use code if text is meant.

Char datatype: More common is a varchar2 datatype; char is always space padded

Varchar: obsolete, either use varchar2, or char.

Never mix varchar2 and chars - it's a sure recipe for disaster!

Year/Time components should be of a Date, timestamp, or ~with TZ datatypes. You could probably combine your offering into one scheduled_date column of the date datatype.

One more piece of advice: when complaining about errors, copy the actual error message - not your interpretation of it. Line and column indicators would have helped.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...