Jonathan Gennick

Jonathan Gennick

press my name for the menu

Writer * Book Editor * Oracle DBA * SQL & PL/SQL Developer

Father * Husband * Son * Mountain Biker * EMT

Teach Yourself PL/SQL in 21 Days
Corrections

Chapter 2

Old form of TTITLE and BTITLE were used.
Several of the listings in chapter two used the TTITLE and BTITLE commands. Unfortunately, the obsolete versions of those commands are used. Worse, the output in the book shows what you would get if you used the new forms. The obsolete forms still work in SQL*Plus, because Oracle has maintained backwards compatibility. The new forms of these commands require a position specification prior to any title text. For example, instead of using the old form as shown here, and in listing 2.2:

TTITLE 'Monthly Sales|for June'

you should really use the new form as shown below:

TTITLE LEFT 'Monthly Sales|for June'

The keyword LEFT tells SQL*Plus to left-justify the titles. In addition, the new form of TTITLE does not translate the vertical bar character into a line-break. Instead you must use the SKIP keyword, like this:

TTITLE LEFT 'Monthly Sales' SKIP 'for June'

You may also use CENTER to create a title centered within an 80 character line, or a RIGHT to create a title that prints flush right in the current line. Use the SET LINESIZE xx command to specify a linesize other than 80, for example:

SET LINESIZE 40

Gives you a 40 character line. Titles are centered and right justified within those 40 characters.

Page 26, Listing 2.1
When you execute this listing, you will get three lines of data per page, not six as the book shows. The column heading and blank line at the top of the page count for three lines, leaving only three for data.

Page 26, Listing 2.2
Be sure to terminate the SELECT statement with a semicolon. The book omits this.

Page 27, Listing 2.3
Be sure to terminate the SELECT statement with a semicolon. The book omits this.

Page 29, the first substitution variable example
The ORDER BY clause reads ORDER BY &sort 1 asc. There should not be a space between &sort and 1. The correct clause is ORDER BY &sort asc.

Page 30, Listing 2.4
The SELECT query in this listing simply could never work the way it is shown. In the WHERE clause, &c_job is used to supply the name of a job title to search for. Yet in the ORDER BY clause, &c_job is used to specify the name of a column to sort on. Unless you happened to have a column name that matched a one-word job title, and you used that name as a value for &c_job, this query is doomed to fail. Change the ORDER BY clause to read ORDER BY job, and the query should work.

Page 31, Listing 2.5
Because the SELECT statement for the break report does not sort the resulting records on the same column specified in the BREAK command, you may end up getting line breaks at seemingly random places. You may even get one after each record. The solution? Add ORDER BY sales_region to the query.

Page 33, Listing 2.6
This has the same problem as listing 2.5. Add ORDER BY sales_region to the SELECT statement.

Chapter 3

Page 45, 4th 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.

Page 46, Listing 3.2
Oracle tightened up the rules for NUMBER subtypes when Oracle8i was released. You may no longer specify a scale when declaring REAL or DOUBLE PRECISION variables. If you're using Oracle8i (release 8.1 or higher), change the declarations of num_dbl and num_real to read as follows:

     num_dbl   DOUBLE_PRECISION(5);

     num_real  REAL(5);

If you are using any Oracle7 release, or any 8.0 release, you should be able to execute the listing as shown in the book.

Page 59, Listing 3.7
You won't be able to execute this trigger. It was included as an example here only for the sake of completeness. Earlier listings showed a PL/SQL function and a procedure. I wanted you to see a trigger as well. However, triggers are not discussed thoroughly until chapter 11.

Chapter 4

Page 73, Table 4.4, the 8th line
Look in the column titled False Expressions. The eigth item down reads "10 <= 20". It should read the other way around, or "20 <= 10".

Page 91, Table 4.11
Table 4.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.

Chapter 7

Page 157, Table 7.7, last line
The date format element DY returns the day of week abbreviated to three characters, not two. The manner in which the format string is capitalized determines the manner in which the abbreviation is capitalized. For example, if the date in question is a Saturday:

DY returns SAT
Dy returns Sat
dy returns sat

And in case you were curious, dY also returns sat.

Chapter 8

Page 191, The section titled "Overloading"
The overloading example given here is confusing. The signature of the two compute_sales procedures are identical. Both accept one DATE argument as an input. Worse, these procedures return arguments! Only functions can do that. Here is a better example:

DECLARE
  FUNCTION check_date (begin_date IN DATE) RETURN BOOLEAN IS
  ...
  FUNCTION check_date (begin_date IN VARCHAR2) RETURN BOOLEAN IS
  ...
BEGIN
  ...
END;

In this example, if you call check_date and pass in a variable of type DATE, the first version of the function will be used. If you choose to pass in the date as a character string, the second version of the function will be used.

Page 198, Listing 8.1
As shown in the book, listing 8.1 will not compile. The listing was not written well, and contains two major mistakes:

  1. The listing should end with a forward-slash on a line by itself.
  2. In the line that reads "set journal.qty = journal.qty + qty", the third occurance of "qty" refers to the qty column in the JOURNAL table, and not to the procedure's argument. To refer to the argument, you must qualify the reference to qty with the procedure name, e.g. "parts.qty".

Here is a corrected version of the procedure:

create or replace procedure parts (qty number)
as begin
  update journal
    set journal.qty = journal.qty+ parts.qty
  where part_id=10;
end;
/

Note that since the book never has you create a JOURNAL table, that you still won't be able to compile this procedure--not unless you create a JOURNAL table yourself.

Chapter 9

Page 213, Listing 9.2
The ALTER TABLE statement in this listing does not use the correct syntax. Either of the following two versions will work:

alter table department
add constraint some_name primary key (dept_id);

alter table department
add (
      constraint some_name primary key (dept_id)
    );

The first version is similiar to what you see in the book. You only need to add a constraint name in order to make it work. The second version represents the syntax that I personally always use. It's more versatile, and allows you to add multiple columns or constraints with one ALTER TABLE statement.

Page 214, Example at top of page
The INSERT statement at the top of page 214 attempts to insert two rows as follows:

INSERT INTO employee VALUES
   (1, 'Jessica Loraine', 2, 8.50, 'H', 3,
    2, 'Kurt Roberts', 5, 100.00, 'S', 3);

That INSERT statement is completely incorrect. It is not possible to insert two rows by supplying two sets of values in the value list. Try to run that statement, and you'll get an ORA-00913: too many values error. In this example, the correct way to insert two rows is through the use of two separate INSERT statements. For example:

INSERT INTO employee VALUES
   (1, 'Jessica Loraine', 2, 8.50, 'H', 3);

INSERT INTO employee VALUES
    2, 'Kurt Roberts', 5, 100.00, 'S', 3);

Page 215, Listing 9.4, First Part
The first PL/SQL code block in listing 9.4 has two problems. First, the i_dept_name variable declaration does not include a datatype. Second, the code block should be terminated with a forward-slash. Here is a corrected version:

DECLARE
  i_dept_id INTEGER,
  i_dept_name VARCHAR2,
BEGIN
  INSERT into department values
  (&i_dept_id, '&dept_name');
END;
/
One note about this listing. The purpose of having you execute this code is to get some data inserted into the DEPARTMENT table. You may find it easier to forget about PL/SQL, and just issue the INSERT statements from SQL*Plus.

Page 225, the section titled PL/SQL Table Delete
This section shows two examples of how to delete entries in a PL/SQL table. The examples read:

emp_tab(2333);
guest_tab(0,10000);

These examples both are missing the DELETE keyword, and should read as follows:

emp_tab.delete(2333);
guest_tab.delete(0,10000);

The keyword DELETE invokes the delete method.

Page 226, Table 9.7, line 4
Line four of listing 9.7 is missing a closing parenthesis. It should read:
dbms_output.put_line(emp_table (row_indicator));

Page 228, fourth code segment
The statement in the book reads:

INSERT into department
values ('ENGLISH','Kathy Johns',
courselist (course(100,'Modern English'),
course(200,'Poetry'),
course(300,'Advanced Modern English'));

The word course should really be courses.

Chapter 11

Page 258, listing 11.9, line 8
The IF statement, that forces updates to be made between the hours of eight and five, is incorrect. The version in the book reads:

IF TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 8 --nothing before 8:00am
OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 5

Since the hh24 in the format string returns the time according to a 24 hour clock, the last part of the IF statement should compare against 17 not 5.

Page 275, the "Warning" box
The warning box on this page refers to two listings numbered 14.22 and 14.23. These numbers are incorrect. The correct listing numbers are 11.22 and 11.23. The reason for this, if you are interested, is that chapter 11 was originally planned as chapter 14. After it was written, things were moved around a bit, and these two listing references inadvertantly were not adjusted.

Chapter 13

Page 295, listing 13.1
There are three mistakes in this listing:

You can download a correct version of listing 13.1 from here: typ13_1.sql.

Page 304, listing 13.5, line 2 of the SELECT statement at the bottom
There was a slight change between the beta and production versions of Oracle8. In order for the SELECT statement in this listing to work, you need to add a table alias for the employee table. The following code, taken as is from listing 13.5, will not work:

select emp_id, emp_name,
home_address.phone_number home_phone
from employee
where emp_id=597;

The following code will work, because it uses an alias:

select emp_id,emp_name,
e.home_address.phone_number home_phone
from employee e
where emp_id=597

Notice that the employee table has been given an alias of e, and that this alias has been used to qualify the reference to the home address phone number.

Chapter 15

Page 361, the note about 2/3 of the way down the page
This note refers to a subdirectory on the CD that is supposed to contain cover images (GIF files) for nine books. That directory was never placed on the CD. If you want to run the examples in the chapter, and you need some files, download books.zip. Place the files that it contains in a directory named books on your hard drive. Note that the listings in the book expect the directory to be "c:\books".

Chapter 18

Page 428, the last sentence in the section about FOPEN
The last sentence in the section describing the syntax for the FOPEN function says that FOPEN returns a value of type UTL_FILE.FILE_HANDLE. That's incorrect. The value returned is of type UTL_FILE.FILE_TYPE. The syntax block at the top of the page has this correct.

Page 437, Listing 18.3
If you pull this listing off the CD, you may need to remove the blank lines in the listing file in order for the CREATE TABLE command to execute properly.

Page 437, Listing 18.4
The file on the CD that corresponds to this listing (TYP18_4.TXT) does not have the 9-digit ID numbers as part of each record. Listing 18.5 expects those numbers to be there. Refer to Listing 18.4 as printed in the book, and make sure that your input file contains those ID numbers. The numbers do not need to match Listing 18.4 exactly, but they must be unique because the ID number is the primary key to the PRES table.

Another problem with the file for Listing 18.4 is that each alternate line in the file is a blank line. Delete these blanks lines prior to running the code shown in Listing 18.5.

Page 438, Listing 18.5
Line 34 of this listing is a comment, but the comment indicator was inadvertently omitted. The corrected version of line 34 looks like this:

-- Commit the additions

The comment indicator is missing not only from the printed listing, but from the file (TYP18_5.TXT) on the CD as well.