CODEWITHSUNDEEP
sqloracle-database
rakeshisu
rakeshisu

Reputation: 179

Boolean giving invalid datatype - Oracle

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

Upvotes: 5

Views: 22421

Answers (5)

Amimo Benja
Amimo Benja

Reputation: 569

When using an Entity class to create the schema, defining the boolean value as below will help

@Column(columnDefinition = "number default 0")
private boolean picked;

Upvotes: 0

Rachcha
Rachcha

Reputation: 8816

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.

Upvotes: 5

user2854207
user2854207

Reputation:

i think u got a below good result

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CJACJGBG

Upvotes: 0

rkh
rkh

Reputation: 863

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

Upvotes: 10

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 4

Related Questions