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?

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

Answer 1

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

Answer 2

Remove the ; from your create table query.

I have shown this in sqlfiddle: click here http://sqlfiddle.com/#!4/a57e9/1