Search code examples
sqlstringoracle11gcreate-table

Error trying to create a new table in SQL

I am doing some SQL exercises and one of them requires me to run the given code that creates a new table

CREATE TABLE TDEPTO_1 (
    NUMDE INTEGER,
    NUMCE INTEGER,
    DIREC INTEGER,
    TIDIR TEXT,
    PRESU INT,
    DEPDE INT,
    NOMDE TEXT,
    CONSTRAINT [PK] PRIMARY KEY ([NUMDE])
);

But when I try to run it in Oracle Aplication Express, it displays this error message

ORA-00902: invalid datatype

Solution

  • The TEXT datatype does not exist in Oracle. You want to use VARCHAR2(n), where n is the maximum number of bytes that your column can allow (maximum 4000 bytes).

    Depending on your Oracle version, INT and INTEGER may work, but it is better to use PLS_INTEGER

    Also, you should remove the brackets ([]) in the definition of the constraint, as this will also generate an error in Oracle.

    Try (change the 100 to the desired text length) :

    CREATE TABLE TDEPTO_1 (
    NUMDE PLS_INTEGER,
    NUMCE PLS_INTEGER,
    DIREC PLS_INTEGER,
    TIDIR VARCHAR2(100),
    PRESU PLS_INTEGER,
    DEPDE PLS_INTEGER,
    NOMDE VARCHAR2(100),
    CONSTRAINT PK PRIMARY KEY (NUMDE)
);