| 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
has anyone experimented with DBMS_APPLICATION_INFO and connection pooling?
----- Original Message -----
From: "Tim Gorman" <tim_at_sagelogix.com>
To: <oracle-l_at_freelists.org>
Sent: Saturday, February 07, 2004 7:16 PM
Subject: Re: Oracle Development - Best Practice
> 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).
>
> 10) 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...
>
> 11) 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
> -----------------------------------------------------------------
>
-- 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:25:00 CST
 |
 |