Is your SQL super fast on ‘your machine,’ but worried it might be less than awesome in production?
We have a new feature in Oracle SQLcl you can use to detect and prevent poor coding habits from making it into production.
This feature is optional, and it’s not on by default.
set codescan on
Once this is enabled, each time you run a SQL statement, your SQL will be parsed and flagged if it matches patterns as defined by the 12 rules listed in the Docs.
I figured we’d take a quick swing through some of the rules…
…but wait, who’s rules are these?
BI Publisher and Oracle Fusion
Business users can use things like BI Publisher and OBIEE to run some reports against their data from various Oracle FUSION apps. Those reports get turned into SQL, and there are patterns of bad behavior over the years that our support teams have identified.
They’ve broken down these patterns into rules that our Java parser can recognize. We’ve then taken these rules back from the Fusion folks and said, hey you’re using our parser, let’s use your rules.
You can read the nitty-gritty behind this if you have access to MOS and Doc ID 2800118.1.
And in THAT note is a link to a PDF Whitepaper that’s an easier read.
Now a few of the rules with examples. Note that my SQL to demonstrate these rules may or may not make sense. I’m generally trying to ‘trip the breaker’ so to speak.
1. Scalar Sub-Queries with DISTINCT or ROWNUM…
select first_name, last_name, ( select distinct department_id from departments where department_name = 'Purchasing' ) dept_id, hire_Date from employees fetch first 10 rows only;
2. Function Calls in WHERE Clause Predicates
select * from employees where upper(last_name) = 'SMITH';
I’m personally guilty of this, A LOT, but I’m also dealing with very small (relatively) data sets.
3. Long in IN-LISTs in SQL Clauses
select * from departments where department_id not in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);