Oracle PL/SQL Best Practices
By Steven Feuerstein


Unconfirmed error reports are from readers. They have not yet
been approved or disproved by the author or editor and represent
solely the opinion of the reader.


This page was updated March 15, 2006.


Here's the key to the markup:

        [page-number]: serious technical mistake
        {page-number}: minor technical mistake
        <page-number>: important language/formatting problem
        (page-number): language change or minor formatting problem
        ?page-number?: reader question or request for clarification


UNCONFIRMED errors and suggestions from readers:


[xv]  last line on page - web address;
The website listed here for getting code examples and downloading the packages
references is WRONG.

Instead of:

    http://www.oreilly.com/orbestprac/

It should be:

    http://examples.oreilly.com/orbestprac/



[33]  Note at bottom of page;
The code segment for Oracle 7 or Oracle 8 does not seem to work as shown:

CREATE OR REPLACE PACKAGE book_data
is
  total_count  INTEGER(10);
  SUBTYPE total_count_t IS total_count;


For this to work for me in Oracle 7.3.2.2 I did:

CREATE OR REPLACE PACKAGE book_data
is
  total_count  INTEGER(10);
  SUBTYPE total_count_t IS total_count%TYPE;



{47}  2nd line;
Should read:
FUNCTION daily_fine RETURN NUMBER;



[56]  Example code:

The variable l_one_day_fine is not declared or initialized in the code.

Additionally, in the fixed code on page 57, l_overdue_count is not 
initialized prior to processing the loop, so the page 57 code is not 
equivalent to the page 56 example.


[57]  First Loop Exit When statement:

The correct syntax of emulating a REPEAT UNTIL Loop specifically
LOOP
   EXIT WHEN statement is

   LOOP
      ... body of loop ...

      EXIT WHEN boolean_condition;
   END LOOP;

However, in this example,
   The EXIT WHEN clause is placed right before the body loop. A
know catchable-syntax-error descriptions generated by Oracle could be
something like this:-

ERR                  *
    PLS-00103: Encountered the symbol "INTO" when expecting one
    of the following:   . ( * @ % & = - + ; < / > in mod not rem
     an exponent (**)   <> or != or ~= >= <= <> and or like
    between is null is not ||   is dangling

Solution:
After I placed the EXIT WHEN clause right before the END LOOP
statement, it ran without giving me any errors.


(75)  Example function book_title in middle of page:

several typos in this function

An extra space is in "l_title"
---------------------------------
IS
  l_ title book.title%TYPE;
BEGIN

the above should be

IS
  l_title book.title%TYPE;
BEGIN


The RETURN in the BEGIN section is wrong
-----------------------------------------
RETURN l_rec.title

the above should be

RETURN l_title


(77)  second bullet in top third of page:

you refer to DBMS_UTILITY.FORMAT_CALL STACK

it should have an underscore between CALL and STACK

that is DBMS_UTILITY.FORMAT_CALL_STACK


{97}  line 15: SELECT title INTO l_title;
The code does not compile as is.
l_title is not delared.
line  15 should read instead:
SELECT title



[101]  Top of the page:

Sample code on top of page 101 looks flawed as it should relate to the sample
code on the bottom of page 100