Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Code Reviews

Re: PL/SQL Code Reviews

From: <>
Date: 15 Sep 2005 06:53:44 -0700
Message-ID: <> wrote:
> Hello all
> Has anyone here ever participated or lead an group in a PL/SQL code
> review?

I reviewed over 2 million lines of third party PL/SQL code in a past contract position. I was the 'one man code review' team. :o)

> My project manager and I believe that our we need to
> incorporation a code review/inspection into our process and I am trying
> to assemble a list of resources that should help us in accomplishing
> this task.

My problem was that the code was already running live but too many problems had been detected, data corruptions were happening and duplication. My job was to find out why - and looking at the code gave me a pretty good clue !

If you are performing a code review as part of the development process then I congratulate you and your manager on some serious foresight !

> Most importantly, I'm looking for sites/white papers that deal with
> either Code Review (the general approach) or specific documents
> pertaining to the process (defect checklists specific to PL/SQL)

I didn't have any documents to hand, other then the Oracle docs, but common sense (badly named if you ask me !) and a good overall knowlege of how Oracle works and what Oracle packages etc exists should be a good help. I did my reviews based on :

.. transaction control : were there rogue commits and/or rollbacks anywhere ?

.. exception handling : was there any? Did it work? Did it handle all exceptions? Were any exceptions simply NULL'd out?

.. Correct use of Oracle built in functions etc - correct calling parameters and so forth.

.. Any wheel re-inventing going on? For example, any code to validate a string as a number but wasn't using TO_NUMBER and exception handling etc.

.. Use of SELECT COUNT(*) ... prior to an UPDATE or INSERT 'choice' rather than doing what was required and trapping exceptions etc or use of EXISTS where absolutely necessary.

.. Bulk operations rather than looping around - ie use of FORALL and BULK COLLECT in PL/SQL to reduce context switching and increase performance.

.. etc.

Hope this helps.

Norm. Received on Thu Sep 15 2005 - 08:53:44 CDT

Original text of this message