| 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
With connection pooling there shouldn't be a problem, but with session
pooling you have the problem that you'd need to change again the app info
for each different request. I don't know whether OCI or JDBC provide
bundling dbms_application_info with the request itself? (I remember that OCI
is able to bundle session_id with request for example).Otherwise we could
have too many calls/network roundrips to Oracle.
Anyway, someone (who is not as lazy as me) should gather reasonable responses to this thread and put it into one document (and possibly post it to cooperative FAQ at www.jlcomp.demon.co.uk), it'd be handy to give to developers...
Cheers,
Tanel.
> 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
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> 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:50:01 CST
 |
 |