Learning Oracle PL/SQL By Bill Pribyl With Steven Feuerstein The 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. 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 This page was updated April 18, 2008. UNCONFIRMED errors and comments from readers: {16} 1st Para. under Section, "Hardware and Operating System"; At "Oracle says you need at least the following: o 256 megabytes of RAM" Oracle's Oracle9i Installation Guide for UNIX (and related Systems) specifies 256MB RAM for a 'Client' Install but 512MB RAM for the Server. See Install Guide, beginning at: http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/unix.920/a96167.pdf Admittedly, this is for the latest release of Oracle9i; however, I believe I remember 512MB RAM also for an earlier Release of Oracle9i. 256MB RAM is sufficient, however, for a Server install of Oracle8i on Linux or for the Personal Edition of 8i on Win2K. [36] 1st paragraph (3rd line); The syntax template for declaring a variable is described in the book as follows: variable_name DATATYPE [ CONSTANT ] [ := | DEFAULT initial_value]; The error is that constant should be BEFORE declaring the DATATYPE. Also initial value should separate from the := and DEFAULT tokens. Here's the correct syntax template: variable_name [CONSTANT] DATATYPE [ := | DEFAULT] [initial_value]; (39) Examples given for Arithmetic Operators; The example given is days_in_first_quarter := 31+28+31+30; As the quarter will be of only 3 months, this should read days_in_first_quarter := 31+28+31; {43} Last line of page; The code IF ss# LIKE '___-__-____' may give you something of the format NNN-NN-NNNN but it will not necessarily make sure the format is EXACTLY like that. For instance, it will also give you anything of the format: CCC-CC-CCCC (C = character) ----------- (- = any puntuation mark) etc... it would be pretty complicated, i think (unless there is an IS_Number function) to come up with a restrictive clause to give you exactly: NNN-NN-NNNN. Perhaps a different, simpler example should be used for this... (47) Footnote; The footnote says "... and the International Standards Organization (ISO)." In fact, ISO is not an acronym or abbreviation, but is an invariant form identifying the standards body. In English, the correct name is International Organization for Standardization. See http://www.iso.ch/iso/en/aboutiso/introduction/whatisISO.html for the full explanation. (58) First para under the heading 'Naming Rules..'; Dec 2001: First Edition NOW: such as variables, procedures, 'variables',and user-defined types. SHOULD BE: such as variables, procedures, 'functions',and user-defined types. Note: Quotes are just for your easy reference. (64) 1st paragraph, 'CREATE TABLE books' SQL is missing a comma on line 5; Missing comma will give an error "ERROR at line 3: ORA-00907: missing right parenthesis" when one trys to create both these tables. CREATE TABLE books ( isbn VARCHAR2(13) NOT NULL PRIMARY KEY, title VARCHAR2(200), summary VARCHAR2(2000), author VARCHAR2(200) *****missing ',' in book***** date_published DATE, page_count NUMBER ); CREATE TABLE book_copies( barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY, isbn VARCHAR2(13) NOT NULL, CONSTRAINT book_copies_isbn_fk FOREIGN KEY (isbn) REFERENCES books (isbn) ); [86] line 39 of 'Unit Tester'; I don't know how to deal with this error, is it me or a flaw in the code?? ERROR at line 39: ORA-06550: line 39, column 4: PLS-00201: identifier 'REPORTEQBOOL' must be declared ORA-06550: line 39, column 4: PL/SQL: Statement ignored ORA-06550: line 70, column 1: PLS-00201: identifier 'REPORTEQBOOL' must be declared ORA-06550: line 70, column 1: PL/SQL: Statement ignored (89) 1st paragraphy; The text shows: Running the unit test results in: book_copy_qty function, zero count: PASSED book_copy_qty function, non-zero count: PASSED book_copy_qty function, null ISBN: PASSED IT should read: Running the unit test results in: book_copy_qty function, zero count: PASSED book_copy_qty function, unit count: PASSED book_copy_qty function, multi count: PASSED book_copy_qty function, null ISBN: PASSED {99} 1st paragraph; Top of page 99 shows the continued results from running the Unit Test Program for the package Test_Book. The second line from the top shows the output as follows: ...weed procedure, book count normal: PASSED. The actual code for the package body is not in the book, but available online at: "http://examples.oreilly.com/9780596001803/individual_files/ch03/test_book.pkb" If you run the code as written, the result above will change to: ...weed procedure, book count normal: FAILED. Expected 0; got 1 The first call to 'reporteq' in the weed procedure appears as follows: reporteq ('weed procedure, book count normal', '0', TO_CHAR (book_count ())); I believe the author meant the call the function 'book_copy_count', not 'book_count', because the weed procedure only deletes from the book_copies table, not the books table. And so the first call to 'reporteq' in the weed procedure should have been written as: reporteq ('weed procedure, book count normal', '0', TO_CHAR (book_copy_count ())); Changing this will make the results match that of the book. (135) middle of the page; The create statement for the lopu package BODY is incorrectly written as: CREATE OR REPLACE PACKAGE lopu AS It should be: CREATE OR REPLACE PACKAGE BODY lopu AS [135] middle of page; The code for the package body will not compile as written in the book. The problem is with the function 'dflt_date_format' which is incorrectly written as follows: FUNCTION dflt_date_format RETURN VARCHAR2 IS RETURN dflt_date_format_private; BEGIN PROCEDURE set_dflt_date_format(new_format_in, VARCHAR2) . .. ...rest of procedure The function needs to be modified as follows (the BEGIN statement needs to appear before "RETURN dflt_date_format_private;", and the END statement is missing): FUNCTION dflt_date_format RETURN VARCHAR2 IS BEGIN RETURN dflt_date_format_private; END; PROCEDURE set_dflt_date_format(new_format_in, VARCHAR2) . .. ...rest of procedure (140) middle of the page; Currently reads as follows: Line 3: Because I want to make sure that the error message fields (lines 16-22).... It should read as follows: Line 3: Because I want to make sure that the error message fields (lines 15-22).... The error message fields begin at line 15, not line 16. (156) final paragraph; The text is "The previous example declares a cursor, tcur, with a single column, due_date.". I cannot see this previous example. The example just covered gives bkcur and bcur and the column would be publication_date not due_date.