Re: scripts for checking naming conventions (prefix etc) for PL/SQL
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: "Bjørn D. Jensen" <bjorn.d.jensen@xxxxxxxxx>
- Date: Sat, 22 Sep 2007 12:50:40 +0200
Ciao Bjørn,
yes, but I strongly believe that automatic scripts are almost useless,
and potentially even dangerous.
To further elaborate - I frequently write procedures in packages that
are even 1,000 lines long, so a script that tries to enforce the rule
"every procedure should be at most 25 lines long" would probably
send an email automatically to HR to have me fired on the spot.
That, until a human read the code and finds that the monster procedure is
-- check customer is authorized
<20 lines of code>
-- lock order line, exception if order doesn't exist
<13 lines of code>
..
that is, basically "in-line subprocedures", each obeying the rule - I find
that this code is easier to follow than the equivalent
check_customer_is_authorized ()
lock_order_line_or_exception ()
not always of course - "it depends".
Things you might check automatically IMHO are
- check that formal parameters are prefixed with p_,
locals with l_, etc - a very simple rule that avoids
name collisions with columns referenced in SQL statements
- check that "when others" are followed by raise (automatically
done in 11g by mean of compiler warnings)
- every table/package belonging to application APP1 should
be prefixed by APP1_ - or belong to schema APP1
- no standalone procedures/functions, only packages
Something like that, but these are all *potential* problems
that an expert will notice without even thinking about it;
so the small benefit automatic scripts might provide doesn't
justify the cost of writing them and modifying your formal
development process flow to include them.
There's another peril: you must be sure that everyone understands
that the scripts are not magic and that are meant to supplement
the reviews only. You must be sure everyone understands the scripts
limitations, what they do and what they don't. And that is an
ongoing process (think new team members, new managers, etc),
and it's way too likely e.g. that a new manager might drop the review
process (seen as "costly") "because we have the scripts".
Me, I'd invest everything in the review process, which provides
a huge return.
HTH
Alberto
On 9/22/07, Bjørn D. Jensen <bjorn.d.jensen@xxxxxxxxx> wrote:
> Hi Alberto!
> I understand your point and you are right, but I think the scripts I look for
> will take out the trivial parts to check, so reviewer can focus on more
> important things.
> I just see automatical checking as an supplement not as an stand-alone
> review.
> Greetings
> Bjorn
>
>
>
> 2007/9/21, Alberto Dell'Era <alberto.dellera@xxxxxxxxx>:
> > On 9/21/07, Bjørn D. Jensen <bjorn.d.jensen@xxxxxxxxx> wrote:
> > (snip)
> > > The scripts are not so important, it's more important for me to get some
> > > ideas about what could be of value to check.
> >
> > IMHO, you should establish a review process instead of relying on
> > automatic scripts: have the most experienced developer(s) review
> > the code, in a joint session with the author.
> >
> > This costs almost nothing (it doesn't take a lot for an expert to spot
> > critical points in the code) and the benefits are invaluable, since there
> > will be a lot of knowledge transfer between the two parties - "why
> > you didn't use a private procedure in this package, instead of
> > declaring it in the package header ? " - "Oh I didn't know it was
> possible,
> > what are the benefits ? " - "Well, the benefits are ..."
> >
> > That would disseminate expertise very quickly in your company/team,
> > and the few things an automatic script might catch will be caught
> > as a by-product.
> >
> > Not to mention the networking benefits : the two developers will get to
> > know each other, and probably get in touch in many occasions,
> > consulting each other, thus further improving the quality of work.
> >
> > Oh, last but not least: knowledge goes both ways, even from a "beginner"
> > to an "expert" - so the "expert" will (not might, will) learn new
> techniques,
> > new tricks, new features, that will be further disseminated in other
> review
> > sessions to others ... "viral" knowledge transfer, the best thing a
> company
> > may wish for. All for the negligible cost of a few hours.
--
Alberto Dell'Era
"the more you know, the faster you go"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
