Oracle SQL: The Essential Reference by David C. Kreines 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 any 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 modified on February 20, 2003. Here's a key to the markup: [page-number]: serious technical mistake {page-number}: minor technical mistake : important language/formatting problem (page-number): language change or minor formatting problem ?page-number?: reader question or request for clarification UNCONFIRMED errors: {9} Last paragraph and code samples (running onto page 10); The example date used is July 4, 2076. If that's the intended case, then: 1. The second line of the sample SQL on page 9 should read: VALUES (TO_DATE('04-JUL-76'); and 2. The second line of the sample SQL on page 10 should read: VALUES (TO_DATE('07/04/76','MM/DD/YY')); [16] Table 1.2; There should be a summary of the CASE ... END construct here or hereabouts; description of DECODE() (page 191) should say "see also CASE in table 1.2" or similar. [85] 1st CREATE syntax statement; The syntax says: CREATE [GLOBAL] [TEMPORARY] TABLE... This should read: CREATE [GLOBAL TEMPORARY] TABLE... There is no mention of the required ON COMMIT DELETE ROWS as or ON COMMIT PRESERVE ROWS clause. Where should this go? Furthermore, the index is missing this, in my humble opinion, key entry "Temporary Table" or "Tables, temporary" entirely. Please let me know where syntactically the ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS belong so I may update my book. [87] Missing; The ALTER TABLE statement does not show the ENABLE/DISABLE clause. For example a construct such as this is not listed. ALTER TABLE xxxx ENABLE CONSTRAINT xxxx_pk or: ALTER TABLE kkkkk ENABLE PRIMARY KEY USING INDEX TABLESPACE tbsp_idx STORAGE (INITIAL 1 M NEXT 500 K) (Note the other disable/enable clauses on the statement only reference the state of the constraint when a column is created or modified.) [91] The example says: ALTER TABLE scott.emp MODIFY (empno CONSTRAINT pk_emp PRIMARY_KEY) ; The first thing wrong with this sample is that 'PRIMARY_KEY' should be 'PRIMARY KEY' (two words). The second thing is that this example won't work. It generates an error every time you try it ("constraint specification not allowed here"). In fact, I've tried many different variations of this statement. Both the O'Reilly documentation and the Oracle Press documentation would lead the casual reader to believe that this should work. However, I believe that the only way to add a constraint to an existing table is via a table-level constraint: ALTER TABLE scott.emp ADD CONSTRAINT pk_emp PRIMARY key (empno) ; The documentation from Oracle does not explain that that this is the only way to add a constraint to an existing table/column. So, if your research verifies this finding, adding a comment about this to "Oracle SQL" would mean that your reference material would be *more* complete than the Oracle documentation. But we knew that, right? [91] Looking at the following web page: http://www.oreilly.com/catalog/9781565926974/errata/orsqlter.unconfirmed with comment: This page was last modified on September 24, 2002 Item #91 says that the book is totally wrong - and that Oracle documentation is totally wrong. While there may be a typo in the book (clearly is) and maybe the example is not valid for primary keys - the reader is wrong on the point about this concept not working for anything. This form you have shown works fine for NOT NULL vs NULL constraints. For example, create table foo (test varchar2(1)); alter table foo modify (test not null); alter table foo modify (test null); {95-96}, the ALTER USER statement is missing the options for ACCOUNT: ALTER USER username ACCOUNT [LOCK | UNLOCK] {116} the ANALYZE flow chart has values reversed. Currently reads: ESTIMATE [SAMPLE integer {ROWS | PERCENT}] STATISTICS Should read: ESTIMATE STATISTICS [SAMPLE integer {ROWS | PERCENT}] {123} middle of page; Syntax of select clause appears to have a missing ']' The '[' for the group by clause never ends. [130] Syntax for UPDATE schema paragraph; I have the Sept 2000 (1st edition) of the book. In the preface page xxviii in the paragraph 'versions of oracle' the author states that all information presented is accurate for oracle version 8.1.6 The syntax for UPDATE suggests that a 'SAMPLE' clause is available. Page 131 suggests that this 'specifies that a random sample of the rows in the table be updated'. I have consulted the documentation for Oracle 8.1.6 and no SAMPLE clause exists for an UPDATE, only SELECT. Hence the syntax prese nted is not correct and the last example on page 132 does not work. [134] COLUMN CONSTRAINT; It lists that the PRIMARY KEY can have one or multiple columns. In fact, you cannot list any column. Another error with the references clause of the foreign key: It now shows REFERENCES table (column, column). That's not possible if it's a column constraint. How can a single foreign key column reference a multi-foreign key column? {145-203} In many of the function examples in Chapter 5, many of the examples use the DUAL predefined table, but the existence and purpose of DUAL is not explained anywhere in the book. [145] none; Please add a summary of analytic functions to the next edition. {208 to 210} explains how to edit the SQL*Plus buffer, but do not mention the command to run the command in the buffer after it is edited (RUN or /) [265] last-but-one line; The syntax for declaring a variable scalar is given as variable_name type [CONSTANT] [NOT NULL] [:=initial value] when it should be variable_name [CONSTANT] type [NOT NULL] [:=initial value] {332} Three quarters of the way down; The (dread) EXPLAIN PLAN operation MERGE JOIN CARTESIAN is not mentioned. To be fair, it does not seem to appear in the Oracle documentation prior to version 9, even though earlier Oracle versions do show this operation in plans. {339} 3rd paragraph; EXPLAIN=username/password should read EXPLAIN=username/password@schema or similar. Default value indication is botched in SYS=(YES/NO) Default value indication (for NO) is missing in RECORD=(YES/NO)