How to stop using SELECT *?

This article is inspired by a tweet of Dave Dustin.

Yes, we can stop using "SELECT *" in Oracle. Even it will also not allow the "INSERT" statement without explicitly specifying columns. It will help us to avoid making invalid procedure/packages/views after adding a column into a table (only if these objects using SELECT statement with "*" or INSERT statement without specifying columns).

It is actually a trick. You need to make an anonymous column which will return an exception and will not allow executing the statement.

Here you go with my test results.

1. Add a column in your table as "dontallowselect    AS (1/0)". Note, "dontallowselect" is user defined name.  

2. It will not allow to insert a record without explicitly specifying column names.

3. Finally, it will not allow to use "SELECT *".

References:

Want a cool trick to stop people using SELECT *  ?

Add an illegal computed column to the end of the table definition. pic.twitter.com/DaSDEDzE5K

— Dave Dustin (@venzann) May 28, 2019

Why is SELECT * considered harmful?

Thanks for reading this article and will welcome your comments. 

-Kumar Sudhir