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:

Tagged pl/sql, sql | Post a Comment | Trackback URI

Viewing 4 Comments

Noons 8 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

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...

reply edit reblog flag record video comment

http://dbasrus.blogspot.com

John Flack 8 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

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.

reply edit reblog flag record video comment

gary 8 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

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.
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.

reply edit reblog flag record video comment

Eddie Awad 8 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

@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.

reply edit reblog flag record video comment

http://awads.net/wp/

Trackbacks

(Trackback URL)

Log Buffer #88: a Carnival of the Vanities for DBAs
March 14, 2008 at 9:55 am

[...] Awad gives us the Oracle SQL and PL/SQL Bad Practices Document, which “. . . contains patterns of bad SQL and PL/SQL ...