9) 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).
- As part of allowing program modules to be cleared for production, review
TKPROF reports from SQL traces from unit-testing. Have one member of a team
of 1-5 people who know how to read TKPROF reports approve or disapprove.
Nothing goes to UAT or production without this approval...
- Peer code reviews should be performed frequently enough to keep "best
practices" document up to date. Peer code reviews should not only ensure
that "best practices" are applied, but also that "not-so-good practices" are
revised or removed and "better practices" are added...
---
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
-----------------------------------------------------------------
Received on Sat Feb 07 2004 - 18:16:26 CST
