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
Possibly related:
- 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
Tagged pl/sql, sql | Post a Comment | Trackback URI
Hope everything is working out...
The problem with most “bad practices” documents is that almost without exception they exceed the boundaries of their own definitions.
Witness the title, which apparently refers to only SQL and PL/SQL and then the following claims which have *nothing* to do with SQL or PL/SQL and are all about database and application *design*:
Storing ROWIDs for later reference,
Storing an empty LOB instead of NULL,
Use of magic numbers and strings instead of NULL.
Talk about confusing the confused…
March 9th, 2008, at 11:56 pm #I’ve done both PL/SQL APIs to do DML and views with instead of triggers. The PL/SQL APIs are mostly generated by Oracle Designer and used for Designer Web PL/SQL application generation. We wrote our own APIs for a few applications and had a major problem with the ones we wrote because they use a parameter for every column in the associated table. This quickly becomes a maintenance nightmare - adding a new column means that you have to change EVERY module that calls the API. If you are going to write an API, Designer’s format is better - two parameters that are PL/SQL records, the first a data record, the second a record with a boolean for each column telling whether to use this value in the DML.
But what I really prefer is views with Instead of triggers. You get all of the benefit of using an API, but you can use any tool that expects to be updating a table.
March 10th, 2008, at 5:38 am #The ‘embedding complex SQL into PL/SQL’ argument is flawed. If you have to change the view, it will still invalidate the dependent objects (ie the package) which will then be recompiled when next invoked.
March 10th, 2008, at 1:54 pm #You’ve also got two objects to deal with. Not necessarily a bad thing if the view can be re-used. But you are likely to be faced with the choice of putting filter conditions within the view itself, or as part of the query against the view.
@Noons: You’re right, some of them are about database and application design, which I think is indirectly related to SQL and PL/SQL as this is how you access and manipulate the application data.
@John: yes, it makes sense to use a tool to generate packages for API access to table data. I believe Quest CodeGen Utility http://qcgu.net/ can do that for you.
@gary: I agree with you. One approach is to wrap all SQL, complex or not, into separate functions.
March 10th, 2008, at 9:08 pm #[…] Awad gives us the Oracle SQL and PL/SQL Bad Practices Document, which “. . . contains patterns of bad SQL and PL/SQL code that Gojko Adzic […]
March 14th, 2008, at 9:55 am #