SQL keeps throwing "missing left parenthesis"-CodePudding

I don't understand what I am doing here but I keep getting a "missing left parenthesis" error in the following SQL code. Can anyone please tell me what I am doing wrong? Newbie here.

CREATE TABLE AGENT_INFO
    (AgentID SMALLINT PRIMARY KEY, AgentName VARCHAR, PhoneNumber VARCHAR);

CodePudding user response：

The VARCHAR data type needs a size (and should probably be VARCHAR2 in Oracle and SMALLINT is an alias for NUMBER(38)):

CREATE TABLE AGENT_INFO(
  AgentID     SMALLINT PRIMARY KEY,
  AgentName   VARCHAR2(10),
  PhoneNumber VARCHAR2(10)
);

If you want a string column that can accept text without specifying a maximum size then use CLOB:

CREATE TABLE AGENT_INFO(
  AgentID     NUMBER(38) PRIMARY KEY,
  AgentName   CLOB,
  PhoneNumber CLOB
);

db<>fiddle here

CodePudding user response：

varchar datatype requires size:

SQL> CREATE TABLE AGENT_INFO
  2      (AgentID     SMALLINT PRIMARY KEY,
  3       AgentName   VARCHAR2(20),
  4       PhoneNumber VARCHAR2(20));

Table created.

SQL>

(Note that Oracle recommends varchar2 instead of varchar.)