Getting error “ORA-00942: table or view does not exist”

Question

I am using Oracle APEX platform, when I run my script code, I got the same error again and again

ORA-00942: table or view does not exist

Kindly help me with find the cause of the error.

create table roles
(
    "role_id" number(5,0) not null,
    "name" varchar2(20) not null,
    constraint "rle_pk" primary key ("role_id")
);

create table users
(
    "user_id" number(5,0) not null,
    "first_name" varchar2(15) not null,
    "last_name" varchar2(15) not null,
    "contact_number" number(15,0) not null,
    "address" varchar2(30) not null,
    "RLE_id" number(5,0) not null,
    constraint "usr_pk" primary key ("user_id")
);

alter table "users" 
      add constraint "usr_rle_fk" 
          foreign key ("RLE_id") references "roles"("role_id");

You have two CREATE TABLE statements here, followed by one ALTER statement. Which statement is generating this error? — Tim Biegeleisen
Not that it should matter, but ROLES is a (non reserved) Oracle keyword. You should avoid using it to name your tables. — Tim Biegeleisen

stefan stefan · Accepted Answer · 2019-05-12T11:18:59

When you write a CREATE TABLE ... statement WITHOUT using "double quotes" for the name of the table, then the table's name will be stored using all UPPERCASE letters.

When you are using "double quotes", then you are enforcing case-sensitivity. That's why the ALTER TABLE statement fails (users <> USERS, roles <> ROLES). You would have to write:

alter table "USERS"  
add constraint "usr_rle_fk" foreign key ("RLE_id")
references "ROLES"("role_id");

Better: avoid double quotes when writing DDL code eg

SQL> create table roles
  2  (
  3   role_id number(5,0) not null,
  4      name varchar2(20) not null,
  5      constraint rle_pk primary key (role_id)
  6  );

Table ROLES created.  -- notice: table name all upper case

SQL> 
SQL> create table users
  2  (
  3      user_id number(5,0) not null,
  4      first_name varchar2(15) not null,
  5      last_name varchar2(15) not null,
  6      contact_number number(15,0) not null,
  7      address varchar2(30) not null,
  8      RLE_id number(5,0) not null,
  9      constraint usr_pk primary key (user_id)
 10  );

Table USERS created. -- notice: table name all upper case

SQL> 
SQL> alter table users add constraint usr_rle_fk foreign key (RLE_id)
  2  references roles(role_id);

Table USERS altered.

When working with APEX (version 19, using SQL Workshop/SQL Commands) you only see the message "Table created" when creating a table, but not the table name as such.

Getting error “ORA-00942: table or view does not exist”

2 Answers