Boolean giving invalid datatype - Oracle

Question

I am trying to create a table in Oracle SQL Developer but I am getting error ORA-00902.

Here is my schema for the table creation

CREATE TABLE APPOINTMENT(
    Appointment NUMBER(8) NOT NULL,
            PatientID NUMBER(8) NOT NULL,
            DateOfVisit DATE NOT NULL,
            PhysioName VARCHAR2(50) NOT NULL,
            MassageOffered BOOLEAN NOT NULL, <-- the line giving the error -->
            CONSTRAINT APPOINTMENT_PK PRIMARY KEY (Appointment)
);

What am I doing wrong?

Thanks in advance

Answer 1

Last I heard there were no boolean type in oracle. Use number(1) instead!

Answer 2

Oracle does not support the boolean data type at schema level, though it is supported in PL/SQL blocks. By schema level, I mean you cannot create table columns with type as boolean, nor nested table types of records with one of the columns as boolean. You have that freedom in PL/SQL though, where you can create a record type collection with a boolean column.

As a workaround I would suggest use CHAR(1 byte) type, as it will take just one byte to store your value, as opposed to two bytes for NUMBER format. Read more about data types and sizes here on Oracle Docs.

Answer 3

Oracle doesn't support boolean for table column datatype. You should probably use a CHAR(1) (Y/N)

You can see more info on this other answer