How to create a table using SQL in Oracle

Question

I'm trying to create a simple table but it's giving me an error says:

Error starting at line 1 in command:
DROP TABLE deleted_employees
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"

Error starting at line 2 in command:
CREATE TABLE deleted_employees
Error at Command Line:2 Column:30
Error report:
SQL Error: ORA-00906: missing left parenthesis
00906. 00000 - "missing left parenthesis"

Error starting at line 3 in command:
(
EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(20 BYTE),
EMAIL VARCHAR2(20 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE) ,
HIRE_DATE DATE ,
JOBE_ID VARCHAR2(10 BYTE),
SALARY NUMBER(8,2),
COMMISSION_ID NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0)
)

Error at Command Line:4 Column:2
Error report:
SQL Error: ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"

Script:

DROP TABLE deleted_employees;
CREATE TABLE deleted_employees; 
(
 EMPLOYEE_ID NUMBER(6,0),
 FIRST_NAME VARCHAR2(20 BYTE),
 LAST_NAME VARCHAR2(20 BYTE),
 EMAIL VARCHAR2(20 BYTE),
 PHONE_NUMBER VARCHAR2(20 BYTE) , 
 HIRE_DATE DATE , 
 JOB_ID VARCHAR2(10 BYTE),
 SALARY NUMBER(8,2),
 COMMISSION_ID NUMBER(2,2),
 MANAGER_ID NUMBER(6,0),
 DEPARTMENT_ID NUMBER(4,0)
 )

I can't figure the problem out, can anyone help?

you should drop if exists, also i think you need to remove the ; from the end of CREATE TABLE deleted_employees; — T I
@marc_s, it's acceptable to specify the length of a VARCHAR2 in bytes or chars - you can choose. The default is typically bytes. — Ben

Alex Poole Alex Poole · Accepted Answer · 2013-05-19T21:54:47

The first error, ORA-00942, is because the table doesn't exist the first time you run this; you're probably expecing that, but it's a bit ugly.

The second is because you have a stray semicolon at the end of the first line of the create statement. The error message isn't entirely helpful but does actually say what is wrong. The rest are knock-ons from that, as it tries to interpret the rest of the command, and can't...

CREATE TABLE deleted_employees
(
 EMPLOYEE_ID NUMBER(6,0),
 FIRST_NAME VARCHAR2(20 BYTE),
 LAST_NAME VARCHAR2(20 BYTE),
 EMAIL VARCHAR2(20 BYTE),
 PHONE_NUMBER VARCHAR2(20 BYTE) , 
 HIRE_DATE DATE , 
 JOB_ID VARCHAR2(10 BYTE),
 SALARY NUMBER(8,2),
 COMMISSION_ID NUMBER(2,2),
 MANAGER_ID NUMBER(6,0),
 DEPARTMENT_ID NUMBER(4,0)
)

How to create a table using SQL in Oracle

2 Answers