ORACLE SQL - invalid data type on table creation

Question

I am trying to set up a table in an ORACLE database and am getting an invalid data type error when I try to run this command:

CREATE TABLE Students (
StudentID NUMBER(8) NOT NULL,
FirstName VARCHAR(25) NOT NULL,
Surname VARCHAR(25) NOT NULL,
Address VARCHAR(100) NOT NULL,
Postcode VARCHAR(7) NOT NULL,
DoB DATE NOT NULL,
Gender VARCHAR(1) NOT NULL,
StudentCategory VARCHAR(50),
StudyType VARCHAR(20),
Nationality VARCHAR(20),
SmokerStatus BOOLEAN,
SpecialNeeds VARCHAR(30),
Comments VARCHAR(30),
PlacedStatus BOOLEAN,
CourseID NUMBER(6) NOT NULL,
AdvisorOfStudies NUMBER(6) NOT NULL,
NextOfKin NUMBER(8) NOT NULL
);

According to the error message, something is occuring 'starting on line 1'. That would mean on the actual create statement itself, rather than any of the data dictionary. I don't understand how this can be causing the invalid data type error.

If anyone can spot what might be causing this, that'd be greatly appreciated!

Error Details:

Error report - SQL Error: ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause:
*Action:

Thanks, Mark

I thought it might be something to do with this, but nothing on Oracle's website specified what might be wrong with this. Can you point me in the right direction @lad2025 — marcuthh
– marcuthh, Commented Apr 10, 2016 at 18:56
Not related to your error but worth noting - from the Oracle SQL Reference: "Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics." — EdStevens
– EdStevens, Commented Apr 10, 2016 at 19:20
Get used to the lack of a Boolean data type in Oracle. At times it is very annoying. You won't be able to write things like "where smokerstatus" or even "where smokerstatus = true" - you will need to make the field either char or number, and use character or number comparisons. — user5683823
– user5683823, Commented Apr 10, 2016 at 21:49
In this topic there a way stackoverflow.com/questions/3726758/… — Roberto Góes
– Roberto Góes, Commented Mar 18, 2019 at 11:26

Atilla Ozgur · Accepted Answer · 2016-04-10 18:57:45Z

9

Change SmokerStatus from BOOLEAN to char(1). Oracle does not have boolean data type . You need to use char(1) or number(1) for this purpose.

SmokerStatus char(1),

answered Apr 10, 2016 at 18:57

Atilla Ozgur

14.8k3 gold badges54 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORACLE SQL - invalid data type on table creation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related