| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle Development - Best Practice
Tim,
As far as his #8, I think he got that from Steve Adams. Steve did some work
a while ago that determined that, all else being equal, after triggers
were cheaper than before triggers. Unfortunately, I don't remember the
details, and the cursory search I just did on IxOra didn't yield any hits.
As I recall, the difference was pretty subtle. People need to keep in mind, when Steve says X is cheaper than Y, no doubt he's absolutely right, but sometimes, these are small, subtle differences. I'm not sure I'd have extrapolate his results into a directive to avoid before triggers at all costs.
-Mark
-----Original Message-----
From: Tim Gorman [mailto:tim_at_sagelogix.com] Sent: Sat 2/7/2004 7:16 PM To: oracle-l_at_freelists.org Cc: Subject: Re: Oracle Development - Best Practice9) Use DBMS_APPLICATION_INFO procedures to "register" a running program by setting the MODULE and ACTION columns in the V$SESSION and V$SQL views. Provide a standard set of code fragments to developers to be executed immediately after the module entry point and before all module exit points (including error handlers).
---
Some notes:
- #1 Applies to every language, including SQL. Just because a language
supports implicit conversions doesn't mean you should ever use it.
- #2 Doesn't make much sense in PL/SQL, because PL/SQL variables are so
easy to use as bind variables -- there just doesn't seem to be any other way
to do things. The danger there is dynamic SQL, where people get carried
away with building the SQL statement in a string and forget to use bind
variables...
- #3 What exactly is meant by "whereever required"? Kinda vague.
Better to say something like "use BULK operations for bulk operations" or
say nothing at all. People can get into a lot of trouble trying to push a
square peg into a round hole unnecessarily...
- #4 "Write code that fits into your screen size"? Assuming the number
of people who develop from a Blackberry are as rare as those who use 35"
monitors, this can still lead to unreadable and unmaintainable code. I
suspect that this would fall out after the first code review or so. Just as
with writing tax policy, strange directives can lead to strange results...
- #5 For one thing, the OTHERS exception must be the last handler
specified, so it's always "the last resort" anyway. And there's nothing
wrong with using declared exceptions (i.e. NO_DATA_FOUND, etc) and defining
your own where pre-defined ones don't exist (i.e. PRAGMA EXCEPTION_INIT),
instead of building up a big IF SQLCODE ... THEN ... ELSIF ... structure...
- #7 "No code change necessary when schema changes"??? Doubt it!!! How
about "fewer code changes necessary"...
- #8 That's a new one by me. Can you prove it?
Hope this helps...
-Tim
on 2/7/04 4:35 PM, jaysingh1_at_optonline.net at jaysingh1_at_optonline.net wrote:
> Dear All,
>
> We are starting a new oracle development project and my boss wants me to
> prepare "Oracle Development- Best practice" document/presentation kind of
> stuff. Basically this is to avoid common mistakes during the development
> cycle.
>
> I have few points..
>
> For example,
>
> 1) While writing pl/sql, use the correct datatype so that implicit conversion
> will be avoided
>
> 2) Use bind variable to avoid unnecessary parsing
>
> 3) Use BULK COLLECT, % ATTRIBUTES wherever required
>
> 4) MODULARITY
> Write the code that fit into your screen size.
> Through successive refinement, you can reduce a complex problem to a
> set of simple problems that have easy-to-implement solutions.
>
> 5) EXCEPTION WHEN OTHERS is almost always a BUG unless it is immediately
> followed by a
> RAISE.Use WHEN OTHERS exception as the last resort and handle exceptions.
>
> For Example,
> EXCEPTION
> WHEN OTHERS THEN
> if (sqlcode=-54) then
> .... deal with it.
> else
> RAISE;
> end if;
>
>
> 6) Tom's Mantra
>
> If (possible in SQL)
> do it;
> else if(possible in PL/SQL)
> do it;
> else if(possible in JAVA)
> do it;
> else
> ..
> ..
> end if;
>
> 7)% ATTRIBUTES
>
> Use %TYPE and %ROWTYPE attributes. No code change is required when schema
> structure changes.
>
> 8) BEFORE VS AFTER TRIGGER
>
> NEVER USE BEFORE TRIGGER FOR VALIDATIONS. Use BEFORE triggers ONLY to
> modify :NEW value.
>
> AFTER row triggers are slightly more efficient than BEFORE
> row triggers. With BEFORE row triggers, affected data blocks must
> be read (logical read, not physical read) once for the trigger and
> then again for the triggering statement.
> Alternatively, with AFTER row triggers, the data blocks must be
> read only once for both the triggering statement and the trigger.
>
> These are only few points w.r.t oracle developers. I like to get more info
> from you.
>
> Your help would be really appreciated.
>
> Thanks
> Jay
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/ms-tnef
-- File: winmail.dat
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Feb 07 2004 - 19:19:05 CST
 |
 |