ORA-00903
In this post, I will introduce 3 main error patterns about ORA-00903, they are:
Create Table
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.
Select Table
Sometimes, you may wonder why the database kept throwing errors but table exists in the database. In fact, the table does exist, but you use it in the wrong way. Let's see some examples.
Suppose that you have created such weird tables in the above, then you should use quotations whenever you query them. Let's see the symptom.
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.
Here we use double quotation marks to wrap the table name.
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
Insert, Update and Delete Table
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 are all data manipulation language on tables. Let's see an example of INSERT which has wrong syntax.
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 UPDATE or DELETE.
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
So the solution is simple, just remove the reserved keyword "table" from the statement, which is unnecessary for DML.
Such error pattern does not result in a syntax alert, instead, it complained invalid usages of table identifiers.
Alter Table
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.