Our product is always tested against Oracle 10g and one of our client insisted to move to Oracle 11g database and shockingly the database creation crashed.On studying the logs we came to the conclusions that best coding practices if followed always helps you to make your product stable, scalable.
What we found is that Oracle 11g does not compile queries that have columns from different table without their corresponding table aliases.
e.g.
This query will work till Oracle 10g only and will fail in 11g and higher versions.This will give "Column ambiguously defined" error message in Oracle 11g and higher versions.
Select Column1,Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;
However the query below will Work in Oracle 10g ,11g and all coming higher versions as this uses best coding practices
Select Table1.Column1 ,Table2.Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;
This clearly shows that best practices if followed give slight pain while implementing but are always fruitful in longer run.
Subscribe to:
Post Comments (Atom)
What about queries that user joins with the USING clause?
ReplyDeleteSelect Column1 from Table1 inner join Table2 USING (Column1);
This won't work on 11g and adding an alias in the select would give an error as well. So it looks like USING is no longer supported in 11g!