Skip to Main Content

Small typos can have big consequences...

Recently I managed to delete every row from a table, while I really only wanted to delete a subset.
What I did was something like this:
This will delete all 14 rows from emp, while I meant to delete just a few rows. Of course this is a silly example for emp/dept, but it illustrates the point.
The reason is obvious: dept does not contain a column named ename, so "select ename from dept" will always return all rows (a bit like "select 1 from dual").

How to avoid this? Easy: either never make a silly mistake like this (easier said than done) OR always use table aliases (or the complete table name). Same goes for views of course.

Some examples: Rowcount: 14 - while you aimed for a subset of those 14.

Rowcount: 14 - if you write it like this, the error becomes a bit more obvious.

Rowcount: 0 - you probably meant to do something like this.

ORA-00904 "dept.ename: invalid identifier" - using table names gives you the exception you need here.

ORA-00904 "d.ename: invalid identifier" - same goes for table aliases, which are a bit nicer to read.

Related tip: always make lots of backups, one day you'll be glad to have them. I sure was :-)

Double or nothing

On database 23c the example below returns an exception: ORA-00904 invalid identifier.
As expected, the double aliases are not accepted.

On database 21xe and 19c however it runs just fine - which I found out after I made a typo but still got a result.
The first part of the double alias is ignored, so iv_d.d.dname actually returns d.dname.