Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: scripts for checking naming conventions (prefix etc) for PL/SQL

Re: scripts for checking naming conventions (prefix etc) for PL/SQL

From: Alberto Dell'Era <>
Date: Sat, 22 Sep 2007 12:50:40 +0200
Message-ID: <>

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

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.


On 9/22/07, Bjørn D. Jensen <> 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 <>:
> > On 9/21/07, Bjørn D. Jensen <> 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"
Received on Sat Sep 22 2007 - 05:50:40 CDT

Original text of this message