Search code examples
sqloracle-databaseplsqlora-06550

Oracle SQL Developer: PL/SQL: ORA-00903: invalid table name

I am getting this error:

Error starting at line 2 in command:
BEGIN
  DELETE * FROM book_copies;
  DELETE * FROM books;
  /* more code here */
END;
Error report:
ORA-06550: line 2, column 10:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 10:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Which is ridiculous as both tables exist in my database. I can do:

SELECT * FROM books;

Or:

SELECT * FROM book_copies;

And both of them work.

Why does Oracle SQL Developer says "invalid table name"?

Solution

  • In a DELETE statement, at least in Oracle, you don't list columns, and FROM is optional. So when you DELETE * ..., it is trying to parse the asterisk as the table name. Note that if you count the columns, column 10 is the asterisk, which is where the invalid table name is being reported.

    Write DELETE FROM book_copies or DELETE book_copies.