Oracle PL/SQL Developer's Workbook
by Steven Feuerstein with Andrew Odewahn


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.

If you have technical questions or error reports, you can send them to
booktech@oreilly.com.  Please specify the printing date of your copy.


This page was last updated April 08, 2003.


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


UNCONFIRMED errors and suggestions from readers:


{40}  The 7th and 8th lines of code in Problem 5-20 now read:

	steven_preferences_so_tasty%ROWTYPE;

	veva_preferences_so_tasty%ROWTYPE;

Should read:
	
	steven_preferences  so_tasty%ROWTYPE;

	veva_preferences    so_tasty%ROWTYPE;

Or:

	steven_preferences tasty%ROWTYPE ;

	veva_preferences tasty%ROWTYPE ;

[41] Example 5-20 appears to have been set up incorrectly.
Currently the text reads:

DECLARE
    CURSOR tasty
    IS
      SELECT ice cream,
       cookie
      FROM favorite_snacks ;
    steven_preferences_so_tasty%ROWType ;

    veva_preferences_so_tasty%ROWType ;


It should read:


DECLARE
    CURSOR tasty
    IS
      SELECT ice cream,
       cookie
      FROM favorite_snacks ;
    steven_preferences tasty%ROWType ;

    veva_preferences tasty%ROWType ;


{42}  At the bottom of the page, after the FETCH statement and before the END 
statement, the cursor "so_tasty" should be closed. This is not really an error 
but good programming practice.


{214}  Snippet (b) of exercise 29-14;
The description for exercise 29-14 reads "...Snippet (a) is superior 
to Snippet (b)" but should be "...Snippet (a) is inferior to Snippet (b)" or 
"...Snippet (b) is superior to Snippet (a)".

Also, the IN parameter should be "indx", not "line".


{245}  1-17;
It is stated that the direct declaration of a subtype with a constrained size cannot
be used to define a variable anchored with %TYPE. This can be done in 8i. In fact you
state in 1-11 pg244  that this restriction was "relaxed" in 8i.

i.e.
DECLARE
SUBTYPE primary_key_t IS NUMBER(6);
mypky primary_key_t;
BEGIN
mypky := 1111111; -- 7 ones
END;

will compile and raise a VALUE_ERROR when executed


(249)  The last sentence in Solution 2-14 now reads:

	"It will, however, cause lots of confusion Andes code..."

Should read:

	"It will, however, cause lots of confusion and this code..."


(257)  The first line of code in the first bullet under 3-17 now reads:

	eypBEGIN

Should read:

	BEGIN


{267}  Solution to question 4-29:

The tenth line of code in the solution for question 4-29 now reads:

IF rate_in not between 0 and 100 THEN

It should read:

IF comm_rate_in not between 0 and 100 THEN


[269]  In Solution 4-36 the 1st sentence now reads:

	"The correct answer is (b)."

Should read: 

	"The correct answer is (c)."

When reraised, the NO_DATA_FOUND exception is propagated from the inner block 
to the outer, where it is successfully handled by DBMS_OUTPUT.PUT_LINE('No data
in outer block');  

So, the final answer is (c):
Starting test, No data in inner block, No data in outer block


{277}  Solution 5-3, 3rd sentence;
The question for 5-3 (page 36) reads:

	"Declare a record that has the same structure as the table called CEO."

The answer for 5-3 reads:

	"DECLARE
   	   ceo_rec ceos%ROWTYPE;"

The table name anchored is incorrectly written as plural, not singular. The 
correct answer should be:

	"DECLARE
   	   ceo_rec ceo%ROWTYPE;"


{277}  Solution 5-4, statement a, now reads:

	"False.  A table-based record..."

However, as the statement is currently worded, this is in fact true.  The 
statement on page 36 reads:

	"A table-based record contains a field for every nonnumeric column in 
	the table."

To be false, the statement would have to be worded something like this:

	"A table-based record contains fields for only nonnumeric columns in 
	the table."

Either the solution or the statement should be altered.


{279}  Answer d:

Technically, the answer should be false.

The assertion is "when you declare a record based on a cursor, you can use 
column aliases to set the names of the record's fields."

Aliases are used when the cursor is declared.

When declaring the record, no new aliases can be used -- fields must be 
basead on column names or aliases as defined in the cursor.


{281}  The 3rd line of Solution 5-11 now reads:
 
	customer_id NUMBER(5) NOT NULL 0,

Should read: 

	customer_id NUMBER(5) NOT NULL DEFAULT 0,

Or:

	customer_id NUMBER(5) NOT NULL := 0,


{285}  As on page 42, at the bottom of the page the cursor "so_tasty" should 
be closed before ending the anonymous block:

	DECLARE
	....
	...
	BEGIN
	   OPEN so_tasty;
	      FETCH so_tasty
	       INTO so_tasty_rec;
	   CLOSE so_tasty;
	END;

[294]  6-24, 6-26;
Files not found on Book's Web Site.  No solution provided.

(375)  First statement inside the WHILE loop (in the discussion for Problem 
14-26, which appears on page 115);

The original version of this problem used a FOR loop with a counter j. As 
part of the problem's solution, the FOR loop has been replaced by a WHILE 
loop, so the counter is no longer needed.  However, the first statement inside 
the WHILE loop still refers to this counter in a call to the function 
get_group_char_cell.


{405}  Solution 16-31;

	"CREATE OR REPLACE TRIGGER employee_upd_t1
	   AFTER UPDATE OF salary
	   ON employee
	   FOR EACH ROW
	   WHEN (old.salary <> new.salary)
	BEGIN
	   employee_pkg.update_emp (:new.employee_id, :new.salary);
	END;"

Must refer to column "sal" instead of "salary."


[546]  Solution 29-12 will not work.

First, there's an "INTO temp_emp_count" statement in a cursor definition.
Secondly, the cursor will return a row even if the count is zero, so "IF
my_cur%FOUND" will always be true.