Mufasa,
You are correct about needing to recompile.
I'm just saying that in this situation it would be wise to do it explicitly.
When doing select * from two different tables, if someone adds a column to table 1, but not to table2, then the procedure won't recompile successfully. If the columns are explicitly named, then the recompile will succeed.
There might be a need to add the new column to the select list, but at least the procedure would run in the meanwhile.
I also said that the select * is valid for a one time anonymous sql block.
Is that clearer? I speak based on my experience of finding the problem with a developers process that suddenly didn't work when they changed the table structure in a select * situation.
That sure brings back fond memories.
Aryeh Keefe