Teach Yourself PL/SQL in 21 Days, Second Edition
Corrections
Day 1
Page 18, Second output block
The numeric result shown in the output block at the bottom of the page
should be 72600, not 65400.
Day 2
Page 26, 2nd paragraph
Each of the numbers in this paragraph have one more zero after the
decimal point than they should. The correct values, in the order that
they appear in the book, are: 0.0000016, 0.000002, 0.00001, 0.00001. Next
edition, I'll use fewer zeros.
Page 35, Variable Naming Rules
Variable names may also contain digits. However, the leading character
must always be a letter.
Page 48, syntax example for the UROWID datatype
In the example, which is halfway down the page, the datatype is shown
as UOWID. It should read UROWID. For example:
employee_row_id UROWID;
Page 58, Listing 2.9
My editors got a bit carried away, and deleted the line break between
lines 2 and 3. The book shows the listing like this:
1: --
2: --3: DECLARE
4: a_name VARCHAR2(30) := 'Jeff Gennick';
...
Notice that lines 2 and 3 have been accidentally combined into one line. The listing won't execute like that. You'll get an error. To correct the problem, enter the listing as follows:
1: --
2: --
3: DECLARE
4: a_name VARCHAR2(30) := 'Jeff Gennick';
...
Page 69, Table 3.4
One of the false expressions shown in this table is wrong, and another
is confusing. The example 10 <= 20 is incorrect.
It's actually true. The example 'Jonathan ' ~= 'Jonathan' really
is a false expression, even though it doesn't appear to be false. It's
a bad example though, to show in the context of Table 3.4. Oracle treats
those two strings as CHAR values, and uses blank-padded comparison
semantics (see page 79, The Datatype's Effect on String Comparisons).
Page 617, Day 2 Quiz, Question 4
The answer to this question states that the maximum length of a VARCHAR2
is 2,000 bytes. That's correct for Oracle7, but in Oracle8 the maximum
length of a VARCHAR2 was bumped up to 4,000 bytes.
Day 3
Page 7, Table 3.5
The last entry in table 3.5 claims that the expression 4
NOT BETWEEN 3 AND 4 is equivilant to: (4
> 3) AND (4 <=4). That second expression is incorrect. It
should be: (4 < 3) OR (4 >
4).
Page 79, Last paragraph
In the sentence that reads: "Trailing spaces alone...between
two springs", the word "springs" should be "strings".
Page 89, Table 3.11
Table 3.11 incorrectly lists the result of FALSE
OR NULL as FALSE.
The correct result for the expression FALSE
OR NULL is NULL.
The reason for this is that the truth or falsity of the expression hinges
entirely on the second value, which is unknown. Hence, the result of the
expression must also be unknown.
Page 97, Quiz question 4
Some line-breaks were deleted from the list of patterns and strings during
the editing process. The list should read as follows:
'123-45-6789'
'___-__-____'
'Boom'
'John%'
'Johnson'
'_oo_'
'517-555-1212'
'Broom'
'Jonson'
'Johnston'
The following pattern matches are true: '___-__-____' matches the value '123-45-6789', '_oo_' matches 'Boom', and 'John%' matches 'Johnson'.
Day 4
Page 101, Syntax for declaring a function
This syntax diagram shows one occurance of {IN|OUT|IN
OUT} for all parameters. That's not correct. These are parameter
modes, and each parameter to a function may have a mode. See The Syntax
for Defining a Parameter on page 103.
Also, the last line of the syntax should have brackets around the function name element. For example:
END [function_name];
The reason for the brackets is that the function name is optional following
the END keyword.
Page 106, Syntax for declaring a stored function
This syntax diagram shows one occurance of {IN|OUT|IN
OUT} for all parameters. That's not correct. These are parameter
modes, and each parameter to a function may have a mode. See The Syntax
for Defining a Parameter on page 103.
Page 109, Third paragraph following the output block
This paragraph should direct you to line 6, column 1, not to column 6,
line 1.
Page 115, Syntax for nested IF statements
The line containing the first ELSE reads as follows:
ELSE <both conditions have
been evaluated to false>
This is misleading. This ELSE corresponds to the most recent IF statement. This ELSE branch will be taken if condition 2 evaluates to false. Condition 1 will have to be true, otherwise control would never hit the second IF statement to begin with.
Day 5
Page 129, Listing 5.1
This listing references a function named PANIC.
That is not an Oracle built-in function. If you correct the invalid GOTO
illustrated in this listing, and and then attempt to execute the corrected
listing, you will get another error about the PANIC
function. You can work around this problem by replacing the call to PANIC
with a call to DBMS_OUTPUT.PUT_LINE.
For example:
DBMS_OUTPUT.PUT_LINE('Panic');
Note that this listing is primarily intended to illustrate an illegal
use of the GOTO statement.
We never intended for you to execute it.
Page 130, Listing 5.2
The label should be <<insideloop>>, not <<insideloop>.
The book is missing the trailing > character.
Page 131, Listing 5.3
See the previous entry for Page 129, Listing 5.1.
Page 132, Listing 5.4 and Listing 5.5
See the earlier entry for Page 129, Listing 5.1.
Page 136, Second Paragraph
The first sentence of this paragraph should read:
It is important to understand that the loop continues to execute until v_Calc > 10.
In the book, "<=" was mistakenly used instead of ">".
Page 136, Listing 5.9
This listing references a function named mypi.
Listing 4.3 had you create this function. If you did not execute Listing
4.3, or if you did not save the results, then substitute 3.14 in place
of mypi.
Page 137, Notebox at top of page
This notebox says that the termination condition of the loop shown in
Listing 5.9 could have been v_Radius
!= 12. That's not correct. The loop would never execute with that
condition. However, you could use v_Radius
= 12, and the loop would execute with the same logic as shown in
the book.
Page 138, Syntax block for the EXIT WHEN statement
The sentence introducing the EXIT WHEN syntax should read as follows:
The syntax of the EXIT WHEN statement is:
In other words, it should refer to EXIT WHEN as a statement, not as a loop.
Page 140, Listings 5.13 and 5.14
See the entry for Page 136, Listing 5.9.
Page 141, Listing 5.12
See the entry for Page 136, Listing 5.9.
Page 142, Syntax for loop labels
The syntax as shown in the syntax box looks like this:
<<label_name1>>
LOOP (FOR, WHILE, LOOP)
...
The parentheses, and the keywords in them, are meant to be annotations
to the syntax. The intent was to show that a loop label can preceed a
LOOP statement, a FOR statement, or a WHILE statement.
Page 150, First paragraph under Practicing Recursion
In the middle of the paragraph, you will see: "number [ms]1".
The "[ms]" in the text should have been replaced by a minus
sign. In other words, it should read: "number - 1".
Page 151, Analysis paragraph
The "[ms]" in this paragraph should be read as a minus sign.
Day 6
Page 164, Top of page, syntax for TO_DATE
There is no special TO_DATE syntax for use with Julian days. The first
argument to TO_DATE must be a character string. If you pass a number instead,
as shown in this syntax block, Oracle will perform an implicit conversion
in order to change that number to a character. Then TO_DATE will convert
the resulting string to a date. I don't recommend relying on implicit
conversions. If you are going to do something like this, use TO_CHAR to
explicitly convert the number to a character string.
Page 165, Listing 6.4, Output
The date output by this listing will be 3-MAR-92, not 10-MAR-92.
Page 165, Listing 6.5, Output
The date output by this listing will be 10-MAR-92, not 3-MAR-92.
Page 169, First paragraph
The first paragraph on this page contains the following sentence:
It's more efficient to store a number in the CHAR or VARCHAR2 format because most systems store it as a single byte (instead of two bytes with a NUMBER datatype), and you do not often perform calculations on this data.
Ignore this sentence. Store numbers using the NUMBER type.
Page 172, The Cause paragraph down near the bottom of the page
This paragraph claims that 88 is the maximum value that a variable declared
as NUMBER(2) can hold. That's incorrect. A NUMBER(2)
variable can hold two digits. Any value from -99 to 99 would fit.
Page 173, Paragraph immediately above Listing 6.14.
The sales percentages are stored in a VARCHAR2(20) field (see the listing),
not a VARCHAR(4) field. A VARCHAR(4) field would not be large enough to
hold a value in the form '33.33'.
Page 175, First paragraph under the DATE Functions heading
Oracle does not store dates in the way that this paragraph describes.
To store values of type DATE, Oracle uses a 7 byte format that is probably
not at all like what you would expect. The details of this format are
described in the Oracle Call Interface Programmer's Guide. Look in Chapter
3, Datatypes.
Page 177, Paragraph immediately preceeding Listing 6.19
After truncation, the time will be 12:00 AM, not 12:10 AM.
Page 177, Listing 6.19, Output
The time shown in the output should be 12:00:00 AM, not 12:10:00 AM. The
TRUNC function truncates the time of day to exactly midnight.
Page 178, Listing 6.20, Output
The time shown in the output should be 12:00:00 AM, not 12:10:00 AM. Truncating
to the quarter also has the effect of removing the time element, effectively
making the time midnight.
Page 178, Syntax for ADD_MONTHS
The correct syntax for the ADD_MONTHS function is:
ADD_MONTHS(date_passed, months_to_add)
The date must always be the first argument, and the number of months to add must always be the second argument.
Page 180, Listing 6.25
This listing does not really demonstrate the problem being discussed in
the text. You really need a month where day 1 is a Monday. Try using a
date of 1-May-2000 instead of 1-Sep-99. Either of the following two examples
should work.
SELECT TO_CHAR(NEXT_DAY(
TO_DATE('1-MAY-2000','dd-mon-yyyy'),
'Monday'), 'MM/DD/YYYY HH:MM:SS AM')
FROM DUAL;
SELECT TO_CHAR(NEXT_DAY('1-MAY-2000','Monday'),
'MM/DD/YYYY HH:MM:SS AM')
FROM DUAL;
The second example is close to what you see in the book, but the first example will execute more reliably because it uses a 4-digit year for the input date, and it uses TO_DATE to explicitly convert the date using the proper format.
When you execute this example using a date of 1-May-2000, which is a Monday, you will get back a result of 8-May-2000. 8-May-2000 is also a Monday, but not the first Monday of the month. Listing 6.26, the next one in the book, shows you how to guarantee that you always get the first Monday of any given month.
Page 181, Listing 6.26
Since you used 1-May-2000 for Listing 6.25, use 30-Apr-2000 instead of
31-Aug-99 for this listing. Use TO_DATE to explicitly convert
the date. See the previous entry for Page 180, Listing 6.25 for an example
of how to do that.
Page 184, Syntax for ROUND
Some underscores are missing from the first parameter. The correct syntax
is:
ROUND(input_date_or_number, rounding_specification)
Page 185, Syntax for TRIM
The correct syntax for TRIM looks like this:
TRIM([LEADING|TRAILING|BOTH]
trim_character FROM trim_source)
BOTH is the default if you do not specify otherwise. The trim_character parameter specifies the character to be trimmed. The trim_source argument represents input string. The function returns the input string minus the trimmed characters.
Pages 186-187, Chapter quiz
Ignore quiz questions 4-7. There's not enough detail in the chapter to
justify asking you these questions in the first place. The information
is there, but it's in the tables, and I don't expect most people to read
all the tables in this chapter.
Page 626, Answer to quiz question #1
The explanation currently reads: "There are many SQL-only commands...".
It should read: "There are many SQL-only functions...".
Page 627, Solution to exercise 9
The third solution shows the date as the second argument to ADD_MONTHS.
That's invalid syntax, and will never work. The date must be the first
argument.
Day 7
Page 193, Listing 7.2
This listing contains several syntax errors. The literal in the SET
clause of the UPDATE statement
should be enclosed within single-quotes, not double quotes. The call to
PUT_LINE should be prefaced
with DBMS_OUTPUT, and there
is no variable named pay_type
to display. Here's a better version of that listing:
CREATE OR REPLACE PROCEDURE emp_change_s(i_emp_id IN integer) AS BEGIN UPDATE employee SET pay_type = 'S' WHERE emp_id = i_emp_id;
DBMS_OUTPUT.PUT_LINE('New Pay Type = ' || 'S'); END;
In the example that follows this listing, the SQL*Plus EXECUTE
statement is used to invoke the procedure. In the example, the ownername
is explicitly specified as USER_01.
Unless you really are working under two different usernames, you don't
need to explicitly specify the owner when you execute a function. Use
EXECUTE emp_change_s instead.
Page 194, Middle of page just prior to Listing 7.3
The last sentence before the listing reads as follows:
Listing 7.3 re-creates the procedure named
parts.
My coauthor missed a change here while preparing the second edition. The sentence should read:
Listing 7.3 re-creates the procedure named
emp_change_s
Page 194, Listing 7.3
The "S" (double-quotes)
in this listing should be in 'S'
(single-quotes). The important thing to focus on here is the use of CREATE
OR REPLACE. Adding OR REPLACE
to the CREATE statement
allows you to overwrite a previous version of the procedure with a new
one.
Page 194, Final text paragraph & example
The final paragraph of text just prior to the example refers to a procedure
named parts_sum. It should
refer to emp_changes_s,
which is the procedure executed in the example. You'll also notice that
the example declares two variables that aren't used. Those are a holdover
from the first edition. What my coauthor wants you to focus on though,
is the syntax used to invoke the emp_change_s
procedure.
Page 195, First paragraph
The second sentence in this paragraph currently reads as follows:
In this case, the stored procedure sum is called with parameters
qtyandwip_nbr.
Here is the corrrected version:
In this case, the stored procedure sum is called with parameter
i_emp_id.
Page 195, Outblock block
The second block of code on page 195 is labeled Output. It should be labeled
Input. Furthermore, the procedure call should be emp_change_s(2),
not parts_sum(qty, :wip_nbr).
Page 209, Second paragraph
Change "use" to "us" in the sentence that that reads:
"Fortunately, Oracle allows use to add...".
Day 8
Page 228, Listing 8.4
The first PL/SQL block in this listing contains a number of syntax errors.Here
is a corrected version of that block:
DECLARE i_dept_id INTEGER; i_dept_name VARCHAR2(32); BEGIN INSERT INTO department (dept_id, dept_name) VALUES (&i_dept_id, '&i_dept_name'); END;
The second PL/SQL block in the listing is correct.
Page 244, Exercise 3
This exercise is a complete mistake, ignore it. It's not even a declaration.
In addition, the exercise answer in the back of the book (see page 632)
does not correspond to what's shown on page 244.