ORA-00903: invalid table name

ORA-00903: invalid table name error occurs when the table name used to refer to a table in the sql query is invalid or does not exist in the Oracle database. A valid table name must start with a letter and can only contain alphanumeric characters and the special characters $, _, and #. The table’s name cannot contain a reserved keyword. The name must contain no more than 30 characters.If a table name does not meet any of the preceding criteria, Oracle will not recognize it as a table name. Oracle considers the name to be an invalid table name. As a result, the error ORA-00903: invalid table name will be thrown.

The above condition should be followed by the table name. If any of the requirements is not satisfied, the table name should be changed to accommodate the condition. Oracle also allows you to create a table name with a special character or by utilizing reserved keywords. When creating and referring to tables in SQL statements, the table name must be surrounded by double quotation marks.

The valid table name should adhere to the Oracle naming standards listed below.

  • The table name should start with an alphabet.
  • Oracle reserved keywords should not be used in the table name.
  • The table name should not be more than 30 characters long
  • Only alphanumeric characters, as well as the special characters $, _, and #, can be used in the table name.


When the ORA-00903 error occurs

Invalid table names are caused by incorrect naming conventions, a missing of double quotation marks, or incorrect SQL syntax. If you create or refer to a table with an incorrect table name, Oracle will not accept it as a table name. It’s unable to locate the database table. The table name should conform to Oracle naming conventions and be included in the appropriate location in the sql query. If you use the right table name in the correct position in the SQL Statement, the error ORA-00903: invalid table name will be resolved.

CREATE TABLE 1EMP(
id int, 
name VARCHAR2(100)
)
Error starting at line : 4 in command -
CREATE TABLE 1EMP(
id int, 
name VARCHAR2(100)
)
Error report -
ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:


Root Cause

The incorrect table name is the result of not following naming conventions, missing double quotation marks, or improper SQL syntax. The database does not recognize the incorrect table name as a table name. Oracle was unable to find the table in the database. As a result, Oracle displays an error message indicating that an incorrect table name was given and could not be found in the database.



Solution 1

The table name should follow the oracle naming conventions. The naming conventions are as follows

  • The table name should starts with an alphabet.
  • The table name should not be an oracle reserved keyword.
  • The table name should not be more than 30 characters
  • The table name can only contain alphanumeric characters as well as the special characters $, _, and #.

Problem A

-- STARTS WITH NUMBER
CREATE TABLE 1EMP(
id int, 
name VARCHAR2(100)
)

ORA-00903: invalid table name
00903. 00000 -  "invalid table name"

Solution A

CREATE TABLE "1EMP"(
id int, 
name VARCHAR2(100)
)
Table "1EMP" created.

Problem B

-- CONTAINS WITH INVALID SPECIAL CHAR
CREATE TABLE !EMP(
id int, 
name VARCHAR2(100)
)

ORA-00903: invalid table name
00903. 00000 -  "invalid table name"

Solution B

CREATE TABLE "!EMP"(
id int, 
name VARCHAR2(100)
)
Table "!EMP" created.

Problem C

--  CONTAINS INVALID RESERVED KEYWORDS
CREATE TABLE SIZE(
id int, 
name VARCHAR2(100)
)

ORA-00903: invalid table name
00903. 00000 -  "invalid table name"

Solution C

CREATE TABLE "SIZE"(
id int, 
name VARCHAR2(100)
)
Table "SIZE" created.

Problem D

--  CONTAINS MORE THAN 30 CHARACTER
CREATE TABLE EMPLOYEE_NAME_WITH_MANAGER_NAME_AND_SENIOR_MANAGER_NAME(
id int, 
name VARCHAR2(100)
)

ORA-00903: invalid table name
00903. 00000 -  "invalid table name"

Solution D

CREATE TABLE EMPLOYEE(
id int, 
name VARCHAR2(100)
)
Table "EMPLOYEE" created.


Solution 2

SQL statements such as select, delete, insert, and update must be written correctly. Oracle could not identify the table name in the query if incorrect syntax was used in the SQL query. As a result, Oracle’s database will throw the invalid table name error.

Problem

delete from table emp;

Error starting at line : 19 in command -
delete from table emp
Error at Command Line : 19 Column : 13
Error report -
SQL Error: ORA-00903: invalid table name
00903. 00000 -  "invalid table name"

Solution

delete from emp;


Solution 3

If a table is created using a special character or reserved keyword enclosed in double quotes, the table shall always be referred to with the enclosed double quotations. If you ignore the double quotes when referring to a table in the select statements, Oracle will be unable to recognize the table name and hence will be unable to locate the table in the database.

The tables with name which are created with special character or reserved keyword must enclose a double quotation while referring in the sql query. The example below illustrates how to refer to a table name using a double quotation mark.

Problem

select * from 1emp;

select * from !emp;

select * from size;

solution

select * from "1emp";

select * from "!emp";

select * from "size";



Leave a Reply