4

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

marcuthh
  • 592
  • 3
  • 16
  • 42
  • 1
    **[Is there a boolean type in oracle databases?](http://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases)** – Lukasz Szozda Apr 10 '16 at 18:55
  • 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 Apr 10 '16 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 Apr 10 '16 at 19:20
  • 1
    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. –  Apr 10 '16 at 21:49
  • In this topic there a way https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases/44589271#44589271 – Roberto Góes Mar 18 '19 at 11:26

1 Answers1

10

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),
Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69