CODEWITHSUNDEEP
sqlstringoracle11gcreate-table
Alfredo Llopis
Alfredo Llopis

Reputation: 3

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

Upvotes: 0

Views: 86

Answers (2)

GMB
GMB

Reputation: 222402

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)
);

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Your current code appears to be SQL Server. In Oracle, there is no data type exactly called INTEGER, but there is a PLS_INTEGER. Similarly, there is no TEXT type, but we can use something like VARCHAR2:

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)
);

Upvotes: 2

Related Questions