Changes are made to books after they have been published. Prior to each printing, the publisher asks me if any changes need to be made and I give them a list. Here are the known issues with the current editions of the books. Depending on the printing you have, these errors may or may not be fixed in your copy.
(with Marlene Theriault)
Base version used for testing scripts: 9.0.1
The following changes have been made to the scripts posted on this site. These corrections will be in the second printing of the book.
Page 24, third line after the listing, change MAX_SGA_SIZE to SGA_MAX_SIZE.
Page 66, first listing, add "alter database" at the beginning of the listing to change this from a code fragment to a full command.
Page 128, change the datafile names in the example (you would anyway if you were executing this code). See pages 176-177 for sample file names.
Page 350, add "execute" as the first word of the first line of the listing
execute STATSPACK.MODIFY_STATSPACK_PARAMETER -
Page 449, change the reference to svrmgrl in the script to sqlplus /nolog instead.
Page 460, last listing, add a comma at the end of the third line:
execute DBMS_LOGMNR.START_LOGMNR(
DictFileName => '/oracle/dictionary.ora',
StartTime => TO_DATE('01-JUNE-2001 12:31:00', DD-MON-YYYY HH:MI:SS'),
EndTime => TO_DATE('01-JULY-2001 00:00:00', DD-MON-YYYY HH:MI:SS'));
Page 461, add a comma at the end of the third line:
execute DBMS_LOGMNR.START_LOGMNR(
DictFileName => '/oracle/dictionary.ora',
StartScn => 125,
EndScr => 300);
(with Marlene Theriault)
Base version used for testing scripts: 8.1.5.1
Page 172, first listing, change
DBMS_RESOURCE_MANAGER_PRIVS
to
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
Page 184, in the first listing example, the number of bytes per row should be 600.
Page 567, there is a reference to the create materialized view log command, which was not included among the commands shown in Appendix A. The command is available online via Oracle's documentation - see Chapter 7 of the Oracle SQL Language Reference Guide, found online at http://technet.oracle.com/doc/server.815/a67779/toc.htm . A free TechNet login is required in order to access that file.
Page 588, in the listing, change
extent_management
to
extent management
and make the same change in the note at the top of page 589.
Base version used for testing scripts: 8.0.2 Beta.
In the next edition, Figure 1-6 will probably go away or be replaced by something less specific. It was not intended to be a detailed diagram but has been interpreted that way. Also, the description of DBWR beneath that figure should not state that that process reads from datafiles; that change had been indicated for a prior printing but does not appear to have been implemented. I'll try again...
There has been a change made to the way in which attributes or abstract datatypes are selected. In 8.0.2 Beta, the base version for the writing of this book, you did not need to use correlation variables when querying attributes of abstract datatypes. See the related article on correlation variables on the Free Articles page that explains the changes. The page affected by this change is page 594. On that page, change the two queries to use correlation variables (the DDL statement is not affected). The corrected queries are:
select Customer_ID, C.Person.Name
from CUSTOMER C;
and
select C.Person.Name,
C.Person.Address.City
from CUSTOMER C
where C.Person.Address.City like 'F%';
On Page 178, in the second listing, remove the line that reads:
analyze table RSEGS compute statistics;
On Page 353, table 10-3, the last line should read:
Cumulative, Complete, or Incremental export.
The associated text section is correct.
On Page 447, in the example add the second line should read
from EMPLOYEE partition (PART2)
The code shown works in 8.0.2, but Oracle changed the syntax later.
On Page 534 and 535, there are two examples of procedure calls (to DBMS_REFRESH.MAKE and DBMS_REFRESH.CHANGE). In each case, the "interval" parameter value must have quotes around it. In the first listing, change
interval => SysDate+7
to
interval => 'SysDate+7'
And make the same change to the second example.
Note: George has placed the scripts for this book over on his web site. Be sure to read the READ ME file. Click here for George's page.
Base version used for testing scripts: 8.0.2 Beta.
In the first printing, there was a problem with the Appendixes on the CD. In later printings, there is an installation problem with the CD. The publisher is working on it and I'll publish a link to their fix when they post it. I've seen the fixed version of the CD and it looks fine to me; it will go out with the next printing. This is completely out of my hands. If you have issues with the CD in your book, contact the publisher.
A reader has pointed out that some of the queries return different results in different versions of Oracle8. For example, the "NAMED TYPE" response of the DESCRIBE command for abstract datatypes (see page 95 for an example) now returns the actual abstract datatype name. This type of problem can't be avoided; Oracle makes many changes with each new version. Because there are multiple supported versions of Oracle out there, it's not feasible to show a set of results that works the same in all versions; as an author I try to use the most current version available during the writing of the book.
Another reader asked about the "COMPRESSED INDEX" entry in the Alphabetical Reference section. There is no way to specify that an index be compressed via Oracle's CREATE INDEX command. This entry refers not to a command but to a concept. In a compressed index, data compression algorithms are applied to indexes during their data storage. As a benefit, the index requires less space, reducing I/O costs. The costs of decompression are paid during the index access - and that's a CPU/memory operation instead of an I/O operation. So accessing a compressed index is theoretically faster than accessing a noncompressed index. However, managing a high volume of index value updates would be difficult and potentially costly. To my knowledge, Oracle has never offered this capability on B-tree indexes although its competitors have. If it does become available, I expect that it would take place in the background and would not be a command option. The one place I know of Oracle using it is in bitmap indexes.
On Page 556, the Cursor FOR loop example shows the declaration of the rad_val variable within the Declarations section. Since this is a Cursor FOR loop, this declaration is unnecessary; the cursor variable will be implicitly declared by Oracle via the FOR clause. Oracle disregards the explicit declaration, so declaring cursor variables in this manner serves only as documentation. I like documenting my variables in this manner, so I put all my variables in the Declaration section. If you find this style confusing or unhelpful (or, for PL/SQL pursists, sacriligeous), leave the cursor variable declarations for Cursor FOR loops out of the Declarations sections of your PL/SQL blocks. This same style is shown on page 523 of the Annotated Archives.
There has been a change made to the way in which attributes or abstract datatypes are selected. In 8.0.2 Beta, the base version for the writing of this book, you did not need to use correlation variables when querying attributes of abstract datatypes. See the related article on correlation variables on the Free Articles page. The pages affected by this change are:
Page 100. The query of the Person.Name attribute must be altered to use a correlation variable. You can rewrite the query as:
select Customer_ID, C.Person.Name
from CUSTOMER C;
Page 101: The explanation of the page 100 query should be modified to include the correlation variable information, and the query shown on that page should be changed to:
select C.Person.Address.Street
from CUSTOMER C;
Page 616: The final query needs to have a correlation variable. Change the query to:
select C.Person.Name,
C.Person.Address.City
from CUSTOMER C
where C.Person.Address.City like 'F%';
Page 617: No change is needed here - DDL doesn't need correlation variables.
Page 630: Add a correlation variable to the method invocation.
On Page 954, second listing, add the following line to the very end of the query:
and Statement_ID = 'TEST';
That way, the start with clause will have the same limitations as the connect by clause.
On Page 178, the query should not use MINUS as a column alias, since that's a reserved word. Use AminusB instead.
On Page 280, the second query, change WORKSKILL to WORKERSKILL.
On Page 578, in the listing, insert an END IF; line before the EXCEPTION line.
On Page 1165, in the SAVEPOINT entry, there is a reference to an init.ora parameter. Remove that line.
Base version used for testing scripts: 8.1.5.1.
The following changes have been submitted to the publisher for correction in the second printing of this book:
Page 63, the second bullet, BETWEEN can use subqueries if they are single-row subqueries. Multi-row subqueries will return errors when used with BETWEEN.
Page 81, second listing, add the word VALUES at the end of the first line.
Page 108, to go along with the break command, you should also add the following command
compute sum of Ext on Report
Page 116, the second listing should read
column WhereItIs heading "Where It Is"
Page 178, the example should use TRUNC in place of ROUND.
Page 1004, for the heading for the second illustration should be column_ref_constraint.
Page 1234, the CUSTOMER table inserts, add the word VALUES at the end of the first line of both inserts.
Page 1247, halfway down, the entry for 03-JUL-01 should say Sam Dye bought a HEFER, not a HEIFER. This is the old spelling, and a spellchecker changed it. It's a small change, but it will impact the report output shown on page 101 if it's not changed back.
Base version used for testing scripts: 8.0.2 Beta.
General note: Oracle introduces new operations and hints with each new version. In the version used for testing, there were no PARTITION operations; those operations are now available in current production versions.
Page 117, second paragraph after the note, the db block size referenced should be 2KB instead of 4KB.
Page 117-118, the discussion of Export/Import assumes that the table will be dropped prior to the Import so the new INITIAL storage parameter will be used when re-creating the table during Import. That is not stated in the discussion.
Page 153, the fourth sentence should read: "SMON does not coalesce free extents in tablespaces whose default pctincrease values are set to zero."
Base versions used for testing: 7.3.4 and 8.0.4.
Here are the corrections that have been submitted for the second printing of the book. Most of these are minor typos Rachel found. These corrections apply only to the first printing of the first edition.
There are no known problems with the content of any of the scripts. Two of the scripts, gen_tbl.sql and gen_view.sql from Chapter 5 (the OBJECTS folder), are not saved as plain text files. You can either
1. Open them in Word and re-save them to your local drive as text files or
2. Open the OBJECTS.ZIP file in the ZIPS folder and use the versions there, which are plain text files.
Page 4. Second-to-last sentence, change "Sample input" to "Sample output"
Page 52. In the second listing, the line that begins with "select SUM" should have an alias of val10, not val12. This typo occurs only in the Annotations; the script on page 49 and on the CD is correct.
Pages 106. At the very bottom of this page, add a note:
You need to delete the generated username comment prior to executing the generated script.
Page 109. Right after the first listing, add a note:
You need to delete the generated username comment prior to executing the generated script.
Page 251. Third text paragraph, first line, change "document all indexes" to "document all triggers"
Page 253, in the code listing, the first occurrence of Lv_Separator should read
Lv_Separator := '(';
The script that contains this code is correct on the CD, and the annotation on page 258 shows the correct version.
Page 334. Last listing, change "next" to "next integer".
Page 340. First paragraph, change the last word from "database" to "tablespace".
Page 390. First line of the Annotations, the script name should be "usr_oprv.sql". This error also occurs on page 391: in the listing, in the third line of the next paragraph, and in the fourth line of the paragraph after that.
Page 484. In the second listing, change "Rem Create a User Tablespace" to "Rem Create Tablespace"
Page 503. First line, the reference is to step 7, not step 6.