SQLTable

How to Resolve ORA-00903: invalid table name

In this post, I will introduce 3 main error patterns about ORA-00903, they are:

  1. Violating Naming Rules
  2. Missing Quotes
  3. Incorrect Syntax

ORA-00903 due to Violating Naming Rules

Without complying with database object naming rules, we can’t create a table with unusual strings in normal way. Let’s see some cases that throw ORA-00903.

First of all, we logon as a normal user who has no powerful system privileges.

SQL> conn sh/sh
Connected.

Using a Reserved Keyword

We would like to create a table named from, but it failed with ORA-00903.

SQL> create table from (c1 int);
create table from (c1 int)
             *
ERROR at line 1:
ORA-00903: invalid table name

This is because reserved keywords cannot be the object identifier.

Starting with a Number

It sounds normal, but actually, table name starting with a number is not allowed.

SQL> create table 123t (c1 int);
create table 123t (c1 int)
             *
ERROR at line 1:
ORA-00903: invalid table name

Starting with a Special Character

Only few special characters are allowed to use to create a database object identifier.

SQL> create table !@#$ (c1 int);
create table !@#$ (c1 int)
             *
ERROR at line 1:
ORA-00903: invalid table name

Solutions

To avoid ORA-00903, you should fully comply with the database object naming rules provided by Oracle documentation.

If you insist to use such unusual name to create your table, you can quote the identifier, which is to use the exact form to force the database to accept those special cases.

SQL> create table "from" (c1 int);

Table created.

SQL> create table "123t" (c1 int);

Table created.

SQL> create table "!@#$" (c1 int);

Table created.

ORA-00903 due to Missing Quotes

Let’s see how we use those tables that were created in the above statements.

Without Quotes

SQL> select * from from;
select * from from
              *
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from 123t;
select * from 123t
              *
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from !@#$;
select * from !@#$
              *
ERROR at line 1:
ORA-00903: invalid table name

All queries threw ORA-00903. That is, no one can be used by the normal way to query tables. Please try the solutions below.

Solutions

You have to use exact form, the same identifier as we provided in table definition to indicate SQL parser to treat the table name as a special case.

SQL> select * from "from";

no rows selected

The above statement is really weird. All I can say is that don’t ever use a reserved word to create a table except that you have a very good reason to do it.

SQL> select * from "123t";

no rows selected

SQL> select * from "!@#$";

no rows selected

ORA-00903 due to Incorrect Syntax

SQL parser always looks for table identifier where it should be. If the syntax of the statement is wrong, it may mistake one name for another.

INSERT, UPDATE and DELETE

INSERT, UPDATE and DELETE are all data manipulation language on tables. Let’s see an example of INSERT which has wrong syntax.

SQL> create table orders (c1 int);

Table created.

SQL> insert into table orders (c1) values (123);
insert into table orders (c1) values (123)
            *
ERROR at line 1:
ORA-00903: invalid table name

Seems no problem? Actually, you don’t have to add table in the third word position in INSERT statement. It made SQL parser take table as the identifier and complained about that you were using a reserved word.

Same error pattern may also occur when DELETE or UPDATE.

SQL> update table orders set c1=null;
update table orders set c1=null
       *
ERROR at line 1:
ORA-00903: invalid table name


SQL> delete from table orders;
delete from table orders
            *
ERROR at line 1:
ORA-00903: invalid table name

Such error pattern does not result in a syntax alert, instead, it complained invalid usages of identifiers.

Adding a Reference Constraint

The syntax of adding a reference key may be a little complex for developers to compose. For an example below:

SQL> alter table orders add foreign key (c1) references (hr.employees.employee_id);
alter table orders add foreign key (c1) references (hr.employees.employee_id)
                                                   *
ERROR at line 1:
ORA-00903: invalid table name

Seems reasonable? Actually, you can’t put the table name inside the last parenthesis, you should move it outside.

SQL> alter table orders add foreign key (c1) references hr.employees (employee_id);

Table altered.

Please note that, before referencing to a table that belongs to other user, you have to be granted with REFERENCES privilege on the table first.

Leave a Reply

Your email address will not be published. Required fields are marked *