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...
John Flack
· 1 year ago
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.
gary
· 1 year ago
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.
Eddie Awad
· 1 year ago
@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.
All Comments
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...
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.
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.
@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.