Oracle SQL and PL/SQL Bad Practices Document
The document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:
- Use of WHEN OTHERS in exception handling.
- Embedding complex SQL inside PL/SQL code.
- Poor PL/SQL error handling.
- Hardcoding the size of PL/SQL variables.
- Not using bind variables.
- Storing ROWIDs for later reference.
- Storing an empty LOB instead of NULL.
- Use of COMMIT or ROLLBACK inside stored procedures or functions.
- Use of magic numbers and strings instead of NULL.
Finally, Gojko makes the case against “wrapping everything into stored procedures”. He argues that instead of encapsulating all read/write access to data inside stored procedures, a better approach is to use views and instead-of triggers. Now that may make Steven nod his head in disagreement.
Have you used views instead of stored procedures to encapsulate data access, including inserts and updates? What do you think about this approach?
Oracle SQL and PLSQL Bad Practice – Get more free documents
More Resources:
- Cleaning Up PL/SQL Practices
- PL/SQL Best Practices White Paper (PDF) By Steven Feuerstein
- Oracle PL/SQL Best Practices and Common Mistakes Videos
- Oracle PL/SQL Best Practices Examples
- Oracle PL/SQL Best Practices, Second Edition Website
—
Related Articles at Eddie Awad’s Blog:
- Oracle Bookmarklets
- Advice regarding the so-called Oracle Voyager Worm
- links for 2006-06-30
- links for 2006-09-15
- Oracle PL/SQL Best Practices and Common Mistakes Videos