Oracle PL/SQL Programming, 3rd Edition
by Bill Pribyl, Steven Feuerstein

The 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.

Here's a 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

This page was updated May 20, 2005.


UNCONFIRMED  errors and comments from readers: 


{40}  Exiting SQL*Plus - AUTOCOMMIT;
SET AUTOCOMMIT - the default is OFF (fortunately !) and it doesn't have anything to
do with exiting of SQL*Plus. Instead, it means that pending changes are/aren't
commited after each INSERT, UPDATE, ... statement.
The EXIT command has these options [COMMIT | ROLLBACK], COMMIT is the
default.


{43}  3rd paragraph;
Current as in book:
SQL> WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK

Should be:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK


{44}  4th paragraph;
"SQL*Plus looks only in the current directory for login.sql"

Not true per this snippet from the SQL*Plus User's Guide and Reference:

SQL*Plus also supports a User Profile, executed after the Site Profile. This file is
generally named login.sql. SQL*Plus searches for the user profile in your current
directory, and then the directories you specify with the SQLPATH environment
variable. SQL*Plus searches this colon-separated list of directories in the order
they are listed.


[91]  Subtitle and following;
Logical AND and OR short-circuit in PL/SQL just as they do in C. I.e., there is no
need to nest IFs. I believe that this language feature is discussed in you Best
Practices book! I do *not* know if short-circuiting is part of the language's
specification, or results from an optimization, i.e., I don't know if it can be
depended on. I do know that if I have two functions, RETURN_TRUE() and
RETURN_FALSE(), which merely return TRUE and FALSE respectively, RETURN_FALSE() will
*not* be called as a result of this construction:

IF RETURN_FALSE() AND RETURN_TRUE() THEN...

Likewise, RETURN_FALSE will *not* be called here:

IF RETURN_TRUE() OR RETURN_TRUE() THEN...

In C/C++ & Java, nested ifs are considered a bad practice.


(92)  middle;
you write:
  IF condition1 AND condition2
  THEN
    ...
  END IF;
The PL/SQL runtime engine evaluates both conditions in order to determine whether the
Boolean expression evaluates to TRUE.

but PL/SQL User's Guide and Reference Release 2 (9.2) states that evaluation short-
circuiting is employed here:

Chapter 2 Fundamentals of PL/SQL -> PL/SQL Expressions and Comparisons -> Short-
Circuit Evaluation:
When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is,
PL/SQL stops evaluating the expression as soon as the result can be determined.


[95]  The case expression example at the top of the page;
Only the first WHEN statement uses THEN. All the other WHEN statements omit the THEN
between the conditional and executable statements.


{98}  1st PL/SQL code statment (nested case statements);
The logic of the bonus code stated on previous pages is not the logic contained in
the nested case statement on page 98 (in the 6/04 print version), but the first
paragraph implies that it is.

This statement should (as far as I can tell) read either:

CASE
WHEN salary > 40000 THEN
   give_bonus(employee_id, 500);
WHEN salary >= 10000 THEN
   CASE
   WHEN salary <= 20000 THEN
      give_bonus(employee_id, 1500);
   WHEN salary > 20000 THEN
      give_bonus(employee_id, 1000);
   END CASE;
WHEN salary < 10000 THEN
   give_bonus(employee_id, 500);
END CASE;

or:

CASE
WHEN salary >= 10000 THEN
   CASE
   WHEN salary <= 20000 THEN
      give_bonus(employee_id, 1500);
   WHEN salary > 40000 THEN
      give_bonus(employee_id, 500);
   WHEN salary > 20000 THEN
      give_bonus(employee_id, 1000);
   END CASE;
WHEN salary < 10000 THEN
   give_bonus(employee_id, 500);
END CASE;


{117}  Syntax of cursor FOR loop;
The FOR phrase in the sytax description is shown as

  FOR record_index IN [cursor_name, (explicit SELECT statement)]

But the intent is that you must supply either a cursor name or an explicit SELECT,
so, based on the conventions, this should read:

  FOR record_index IN {cursor_name |(explicit SELECT statement)}


{157}  2nd line;
Here is an example of declaring a numeric datatype that can have only one of three
values (1, -1, and NULL):
(...)

Should read :
one of four values (1, -1, 0 , and NULL):
(...)


{205}  4th paragraph;
I think that sentence "INSTR2 works like INSTR4, but allows you to search for UCS-4
code units" should be: "INSTR2 works like INSTR4, but allows you to search for UCS-2
code units.


[283]  bottom;
The to_char section mentions that to limit the factional time component when using
TO_CHAR you must introduce a timestamp(5) WITH TIME ZONE variable to be able to print 
just 5 fractional positions.  That is not correct. You use the FFn format where n is the
number of positions you want to display.

This code illustrates that:
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF5 AM TZH:TZM') FROM dual;

Here is the book sample altered.  Works for me on 9iR2.
DECLARE
  a TIMESTAMP WITH TIME ZONE;
BEGIN
  a := TIMESTAMP '2002-02-19 13:52:00.123456789 -5:00';
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(a, 'YYY-MM-DD HH:MI:SS.FF5 AM TZH:TZM'));
END;
/


{283}  Fifth bulleted example;
When describing the formating of TIMESTAMP variables the text claims that the time
can be specified to the millisecond, but the example output shown below it (and on
the next 2 pages) has the time displayed to the nanosecond.  It also appears from the
examples that the default timestamp precision is to the microsecond (as shown below
the text in question and on page 285).


{328}  example in 2nd bullet;
The example shows:

TYPE mishmash_rectype ...
(emp_number (10) NOT NULL...

However, on P327, it is stated "... a field in a record be NOT NULL 
(in which case you must also assign a default value"
There is no default value specified in this example.


(331)  2nd example;
The bullet point is missing at the start of this example.


{344}  10 lines from bottom;
Color_array_t has not been introduced.
should be:
Color_tab_t


{417}  1st paragraph;
Text tells us we must use DBMS_LOB.FILEOPEN and DBMS_LOB.FILECLOSE when accessing
BFILEs. Code then uses DBMS_LOB.OPEN and DBMS_LOB.CLOSE. Don't know which is correct
(all 4 are in the DBMS_LOB package).


{456}  Table 13-2;
Description for SQL%NOTFOUND is incorrectly a copy of SQL%FOUND.


{493}  Line 27 of code example;
The code reads:

RETURN level_out;

It should read:

RETURN retval;


{507}  Last example;
References to the cursor "major_polluters_cur" in the main body of the code is
missing the "_cur" from the end of the identifier,

i.e.
names (major_polluters%ROWCOUNT) := bad_car.NAME;
should read
names (major_polluters_cur%ROWCOUNT) := bad_car.NAME;

Or, remove the _cur from the declaration as in the example on the following page.


{508}  3rd paragraph;
The select statement is wrong:

the example query reads:

SELECT name, mileage
  FROM transportation
  BULK COLLECT INTO names, mileages
    WHERE TYPE = 'AUTOMOBILE'
      AND mileage < 20;

The 'BULK COLLECT clause is out of place.  It should read:

SELECT name, mileage
  BULK COLLECT INTO names, mileages
  FROM transportation
    WHERE TYPE = 'AUTOMOBILE'
      AND mileage < 20;


{527}  1st Sentence of the "Scope of cursor object";
The sentence reads "The scope of a cursor variable is the same as that of a static
cursor: the PL/SQL block in which the variable is declared (unless declared in a
package, which makes the variable globally accessible).

The parenthetical portion of the sentence appears to contradict the first bullet
point under "Cursor Variable Restrictions" on page 530:

"Cursor variables cannot be declared in a package because they do not have a
persistent state."

This is supported by the Oracle documentation:

.PLS-00994 Cursor Variables cannot be declared as part of a package

Cause: An attempt was made to declare a cursor variable in a package specification,
which is not allowed. Although REF CURSOR types can be defined in a PL/SQL block,
subprogram, or package, cursor variables can be declared only in a block or
subprogram.

Action: Move the cursor variable declaration into a PL/SQL block or subprogram..

I think the authors meant to say that the REF CURSOR type could be define in the
declaration section of the package body (which would make it available globally).
However, the declaration of the actual cursor variable must table place within a sub-
block of the package body.


{547}  Binding code sample at bottom of page;
Unless I misunderstand, the binding code sample at the bottom of this page is wrong.
I'm referring to this snippet:

/* Binding */
EXECUTE IMMEDIATE
    'UPDATE employee SET salary = :val
      WHERE hire_date BETWEEN :lodate AND :hidate'
    USING v_start, v_end;

Shouldn't v_sal be in the USING list? That is, I think the code should read:

/* Binding */
EXECUTE IMMEDIATE
    'UPDATE employee SET salary = :val
      WHERE hire_date BETWEEN :lodate AND :hidate'
    USING v_sal, v_start, v_end;




(564)  "What Are NDS and DBMS_SQL Good For", 2nd bullet point;
The second bullet point should reference the previous section, not the next for the
section on "Eyeballing Equivalent Implementations".


(576)  2,3,5;
STRUCTURE OF A FUNCTION section on page 576:

name
  the name of the function (not procedure) comes directly after the keyword FUNCTION.

parameters
  An optional list of parameters that you define to both pass information into the
function (not procedure) and send information out of the function (not procedure)
back to the calling program.

AUTHID clause
  Determins whether the function (not procedure) will execute under the authority of
the definer (owner) of the function (not procedure) or under the authority of the
current user.


(657)  Line 2 of the CREATE TRIGGER syntax pattern;
{BEFORE | AFTER|
should be: 
{BEFORE | AFTER}


{658}  1st bullet item;
The first sentence of bullet reads, "The following BEFORE INSERT trigger captures
audit information for the CEO compensation table." while the trigger code clearly
shows it as an AFTER INSERT trigger.


{658}  Code sample in section "The WHEN clause";
PRINT VERSION:
  WHEN ((OLD.salary != NEW.salary OR
          (OLD.salary IS NULL AND NEW.salary IS NULL))
    OR (OLD.commission != NEW.commission OR
          (OLD.commission IS NULL AND NEW.commission IS NULL)))

SHOULD BE:
  WHEN ((OLD.salary != NEW.salary OR
          (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR
            (OLD.salary IS NOT NULL AND NEW.salary IS NULL))
    OR (OLD.commission != NEW.commission OR
          (OLD.commission IS NULL AND NEW.commission IS NOT NULL) OR
            (OLD.commission IS NOT NULL AND NEW.commission IS NULL)))


{667}  set_score example;
In print, the example for set_score is incorrect, and should be changed to that given
in the downloaded examples.


{668}  SQL*Plus command line samples towards top of page;
The first SQL*Plus INSERT INTO command should not raise an exception at all.  The
frame being inserted is not designated as either a strike or a spare.  The score of 5
should be perfectly acceptable.  In addition, the error message in the printed
version can only apply when the value for the "strike" column is specified as 'Y'.
Furthermore, the error text
  Score For Strike Must Be Less >= 10
doesn't make sense, nor was it defined in the preceding narrative.


As for the second SQL*PLUS INSERT INTO command...

PRINT VERSION:
  ORA-20001: ERROR: Score For Strike Must >= 10

SHOULD BE:
  ORA-20001: ERROR: Score For Strike Must Be >= 10


[688]  2nd paragraph;
The description of the 'AFTER SHUTDOWN' trigger goes against the rules described by the author on the preceding page! 
I suspect this should read 'BEFORE SHUTDOWN'.



(693)  Line 1 of code sample at bottom of page;
PRINT VERSION:
   1  CREATE [OR REPLACE TRIGGER] trigger_name

SHOULD BE:
   1  CREATE [OR REPLACE] TRIGGER trigger_name


{example code - ndsutil.pkg}

   FUNCTION openFor (sql_string IN VARCHAR2) RETURN cv_type
   IS
      retval cv_type;
   BEGIN
      OPEN retval FOR sql_string;
   EXCEPTION
      WHEN OTHERS
      THEN
         showerr ('openFor', sql_string);
         RAISE;
   END;

should be?

   FUNCTION openFor (sql_string IN VARCHAR2) RETURN cv_type
   IS
      retval cv_type;
   BEGIN
      OPEN retval FOR sql_string;
	RETURN retval;
   EXCEPTION
      WHEN OTHERS
      THEN
         showerr ('openFor', sql_string);
         RAISE;
   END;


[741]Last statement line
The last statement line is not correct:

original:
ALTER [ PROCEDURE | FUNCTION | PACKAGE BODY ] program_name COMPILE DEBUG;

The keyword "BODY" for packages must be at the end of the statement:
correct: 
ALTER [ PROCEDURE | FUNCTION | PACKAGE ] program_name COMPILE DEBUG [ BODY ];


[800]  Table entry for *.FMT;
The description for the file extension *.FMT is not correct:

original: "..., however, any PL/SQL appears only in hex".

This is not correct, because in Forms FMT files any PL/SQL code appears in 
ASCII code (which is readable).


(807)  1st paragraph;
First set of brackets states "(notice the exception handler section that closes the
cursor instead of leaving it hanging open - it's easy to forget housekeeping like
this)", however, there are no cursors involved in the referenced code, and there is
no closing of cursors code.



(853 )  Last complete sentence on the page.;
PRINT VERSION
  The alternative to MAP is an ORDER member function, which compares two methods:
SELF, and another object...

SHOULD BE
  The alternative to MAP is an ORDER member function, which compares two objects:
SELF, and another object...