Oracle PL/SQL Programming, Fourth Edition by Steven Feuerstein, Bill Pribyl 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 June 17, 2008. UNCONFIRMED errors and comments from readers: {71} About 10 lines from the bottom; The text says: 'The notion of the time interval is yet another feature introduced in Oracle Database 10g;' It seems to me it is introduced in Oracle 9i Database. {119} Last sentence before the heading 'Loop Labels'; cursor_name.column_name should be record_name.column_name {132} Code at top of page; Error numbers assigned to CONSTANT variables are not used. I believe the intent was to use the variable names (instead of hard coding the error numbers) in the subsequent PRAGMA declrations. So PRAGMA_EXCEPTION_INIT(exc_too_young, -20001); should be PRAGMA_EXCEPTION_INIT(exc_too_young, en_too_young); and PRAGMA_EXCEPTION_INIT(exc_sal_too_low, -20002); should be PRAGMA_EXCEPTION_INIT(exc_sal_too_low, en_sal_too_low); (175) last paragraph; I am referring to third edition published September 2002. The last sentence of the last paragraph on page 175 seems incomplete. The paragraph discusses the rowidtochar function and how it converts a binary value of type rowid to a string of type varchar2. Then it states that the format of the return string is BBBBBBBB.RRRR.FFFF where each section represents a number in hexidecimal format and then ends with this sentence: All three numbers are in hexidecimal format; for example: 11/14/1994 1988 2018 and with that the paragraph simply ends. It appears to be incomplete and I don't find any reference to this in the other errata that has been published about this book. {204} 5th paragraph - sample output ("The output is:"); The final line of output is listed as: ".....Hi there!....." This output is for: DBMS_OUTPUT.PUT_LINE( TRIM(x) ); which is supposed to have a default trim character of the space. If that's the case, then the output should be: ".....Hithere!....." (211) Last paragraph; I know this doesn't qualify as errata, but I think it's important. The sample regular expression used to demonstrate greediness is: .*, The problem is that the period (.) is never explained to mean any valid character. It confused me, but I was able to look it up easily elsewhere. {259} Tip, Suggestion, Note section - smaller print; The internal representation of the timestamp and timezone should be 2002-02-06 20:00:00:00:00 -0:00, and not => 2002-02-06 20:00:00:00:00 -5:00. Since this is in UTC time. [289] line -6~-4; 1 select to_timestamp_tz('02-APR-07 08.45.58.270000 AM -07:00') as tz 2 , cast( to_timestamp_tz('02-APR-07 08.45.58.270000 AM -07:00') 3 as timestamp with local time zone) as lcl_tz 4 , to_timestamp_tz('02-APR-07 08.45.58.270000 AM US/EASTERN') as tz 5 , cast( to_timestamp_tz('02-APR-07 08.45.58.270000 AM US/EASTERN') 6 as timestamp with local time zone) as lcl_tz 7* from dual DWP1@edwdev SQL+> / 02-APR-07 08.45.58.270000000 AM -07:00 02-APR-07 10.45.58.270000 AM 02-APR-07 08.45.58.270000000 AM US/EASTERN 02-APR-07 07.45.58.270000 AM (353) 3rd paragraph; Says, "When using a nested tables ..." Should say, "When using a nested table ..." (354) Code block, lines 24, 27, & 28; Calls to function "descriptions" should be "description". This occurs on code lines 24, 27, & 28. (373) 1st paragraph under "Nested Table Multiset Operations"; Line 3 reads, "...nested tables (and only, for time being, to nested tables." It should read, ""...nested tables (and only, for time being, to nested tables)." Missing the closing parenthesis. (413) 2nd paragraph under "The XMLType Type" header; 2nd sentence is "Staring with Oracle9i ..." It should be "Starting with Oracle9i ..." (434) 3rd paragraph; Under the title "Record-based updates": "...The following example INSERTS a row into the books table ... " should read "...The following example UPDATES a row ... " (439) Paragraph preceding figure 14-3; Reads, "If I needed to insert 100 rows ..." Should read, "If I needed to update 100 rows ..." {458} 3rd paragraph (Codesection Package Body); Procedure Putline inserts into recently created table logtab (p. 457). However, this insert does not match the table. Columns machine and program is missing. The "file on web" points to log.pkg which does exist, but rather uses log81tab and so on. Here are the missing columns (but this code does not work anyway) It seems that there is an update of the code in progress and only half of it has made it all the way. Either of the code fragments work. (477) 3rd line; It seems to me that 'RETURN level_out;' should be changed in 'RETURN retval;' {477} 3rd paragraph; line 27 of the FUNCTION jealousy_level returns a value (level_out) that is not declared in the function (478) 3rd line under 'Declaring cursors in packages'; It seems to me that 'not within a particular procedure or function in the package' better be left away. It seems to me it suggests cursors can't be declared in procedures or functions. (485) Tip section at top of page; The URL reference to Gennick's "Open Cursors" article is no longer valid. (491) Example cursor code; The cursor is declared as "major_polluters_cur", but then referenced as "major_polluters". {493} 2nd code example at top of page; The "l_transportation" variable is referenced without first being declared. {498} 3rd paragraph; The OF list of the FOR UPDATE may not just for document. I ran into problems with it. There is an old code in our applicaton to update the column; it failed to update the column not in the OF list couple of month ago. It started to work again with the columns being put in the list. It may be an Oracle bug or config problem. Anyway, there is no harm in putting the columns in the list. [521]Second code sample; The function tabcount needs a RETURN retval; after the EXECUTE IMMEDIATE statement, otherwise the function would not compile. {529} Code in Method 3; I think two lines of code in the Execute Immediate example in the above method are in the wrong order. The book says: execute immediate 'select last_name, salary from employee where department_id = :dept_id' using 10 into l_last_name, l_salary; which doesn't work when copied into code. I think it should read: execute immediate 'select last_name, salary from employee where department_id = :dept_id' into l_last_name, l_salary using 10; which does. {612} In the sql where the function is first illustrated in the from clause; In line 6 of the SQL, the "AS pet_nt", presumably an alias after the function call, causes Oracle to tell me that I'm missing a right parenthesis. When I take this alias out of the code, it works perfectly as written without other modifications. I'm running against Oracle 10.2.0.1.0, and the error happened both in Toad and in SQL Plus. {642} Code example on the bottom; The 2 lines in the loop: EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; are in the wrong order, they should read: FETCH book_info.bytitle_cur INTO onebook; EXIT WHEN book_info.bytitle_cur%NOTFOUND; {644} Code example on the top of the page; The order of the lines inside the loop: EXIT WHEN ... FETCH personnel ... should be changed - first fetch next check if the record was fetched. The way it currently is the last row fetched by the cursor will be processed twice. See also error on page 642. {649} code of givebonus2.sp; In line 14 l_row := l_array.FIRST; has to be l_row := l_employees.FIRST; {999}middle; Page 999 says about the TREAT function "[TREAT] will return either the downcasted object or NULL - but not an error". That is true if TREAT is used in SQL, but if TREAT is used in PL/SQL and the object is not of the required type, TREAT does indeed raise an error. See http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i479093 "If p is a person who is not a student, or if p is NULL, TREAT returns NULL in SQL or, in PL/SQL, raises an exception." (1021)First sentence; "The alternative to MAP is an ORDER member function, which compares two METHODS: SELF, and another object..." should be "The alternative to MAP is an ORDER member function, which compares two objects: SELF, and another object..."