Oracle PL/SQL Programming

Errata for Oracle PL/SQL Programming

Submit your own errata for this product.


The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page xxxvii
4th paragraph

My lastname is not spelled correctly. in the book it is spelled "nuiTJen" while it should be "nuiJTen". The "J" and "T" are in the wrong place.

Note from the Author or Editor:
Please change spelling of name to Nuijten.

Alex Nuijten  Oct 06, 2009  May 06, 2011
Other Digital Version
g11n.sql
Online Download

The script file g11n.sql for Chapter 25 is not saved in Unicode encoding. Therefore all Japanese characters are in question marks. In addition, on line 869, the function is created with no valid name, instead of "date_format_func" as earlier fourth eduction did.

Note from the Author or Editor:
Agree with the problems stated. This file on the O'Reilly web site needs to be corrected and replaced.

Jiangping Wang  Apr 23, 2010 
PDF
Page xxxii
2nd paragraph, Part VI, Advanced PL/SQL Topics.

an guide SHOULD BE a guide

Note from the Author or Editor:
Agree. Print edition page number is xxx.

Bhavesh  May 26, 2012  Oct 12, 2012
PDF
Page 34/35
Last paragraph

Adbode Acrobat Reader says that this typo is on page 34 but book reads 35. $ORACLE_HOME/qlplus/admin/glogin.sql IT'S ACTUALLY: $ORACLE_HOME/{S}qlplus/admin/glogin.sql

Note from the Author or Editor:
Agree.

Bhavesh  Jun 02, 2012  Oct 12, 2012
Printed
Page 37
2nd para

It says WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK However, above does not work. Here is right use of statement: WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

Note from the Author or Editor:
Make change as described above.

Eric J Kim  May 26, 2010  May 06, 2011
PDF
Page 39
3rd line

About the erratum I submitted on Jun 02, 2012. Let me correct my mistake first. SHO ERR Errors for FUNCTION WORDCOUNT: SHOULD BE: SHO ERR <<NEW LINE>> Errors for FUNCTION WORDCOUNT: I know I should've read the line between :). Anyway. I was thinking that you should have a third page which points out mistakes of submitters. I mean just because we''re wrong doesn't mean you have to stop teaching :).

Note from the Author or Editor:
Confirmed. Change to: SQL> SHO ERR Errors for FUNCTION WORDCOUNT LINE/COL ERROR

Bhavesh  Jun 06, 2012  Oct 12, 2012
Printed
Page 70
Middle of page

Datetime literals (available since Oracle Database 10g Release 2) should also be mentioned (see http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm). The last paragraph on the page may need to be reformulated accordingly or removed.

Note from the Author or Editor:
REPLACE THIS TEXT... Even though the database allows you to specify intervals using a literal format, you cannot do so with DATE datatypes; notice that '01-OCT-1986' is listed as a string rather than as an Oracle DATE. Yes, PL/SQL or SQL can implicitly convert '01-OCT-1986' to and from Oracle’s internal date format,† but you will normally use built-in functions to perform explicit conversions. For example: TO_DATE('01-OCT-1986', 'DD-MON-YYYY') TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 &#8722;6','DD-MON-YYYY HH24:MI:SS TZH') Both expressions return October 1, 1986, with zero hours, zero minutes, and zero seconds; the first in the DATE datatype, and the second in the TIMESTAMP WITH TIME ZONE datatype. The second expression also includes time zone information; the &#8722;6 represents the number of hours’ difference from GMT (UCT). †As long as the database or session has its NLS_DATE_FORMAT parameter set to DD-MON-YYYY. ...WITH THE FOLLOWING TEXT: To specify literal values of dates and times, you can use the DATE or TIMESTAMP keyword: DATE '1986-10-01' TIMESTAMP '1986-10-01 00:00:00 -6:00' Both expressions return October 1, 1986, with zero hours, zero minutes, and zero seconds; the first in the DATE datatype, and the second in the TIMESTAMP WITH TIME ZONE datatype. The second expression also includes time zone information; the -6 represents the number of hours’ difference from UCT. Alternatively, you can convert strings to date or timestamp datatypes using Oracle built-in functions. The following are the equivalent of the above literals: TO_DATE('01-OCT-1986', 'DD-MON-YYYY') TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH') In your code, a simple string such as '01-OCT-1986' <i>may</i> get converted to a date datatype; if that happens Oracle will apply the current session's value of the NLS_DATE_FORMAT parameter when attempting the conversion. In this case, only if the format were set to DD-MON-YYYY would you be guaranteed to get the desired result. You probably want to avoid such implicit conversions, since NLS_DATE_FORMAT can change.

Anonymous  Sep 21, 2011  Oct 12, 2012
Printed
Page 103
First code example (continuing from previous page)

In the example for "cursor FOR" loop FOR l_current_year IN ( SELECT * FROM ... l_current_year automatically becomes a record type, not a field, and yet it looks like it is later used as a (numeric) field when passed to display_total_sales procedure. If the comment about display_total_sales procedure now accepting a record rather than a number (as in previous examples) is true, then the variable name, l_current_year, is very misleading. I write this based on the 4th edition; I do not actually have access to the 5th edition, and this page is not available on Amazon.com book preview. However, I believe that this is left unchanged since the 4th edition.

Note from the Author or Editor:
Change the block of code at the top of 103 to: BEGIN FOR sales_rec IN ( SELECT * FROM sales_data WHERE year BETWEEN start_year_in AND end_year_in) LOOP display_total_sales (sales_rec.year); END LOOP; END display_multiple_years;

Anonymous  Sep 21, 2011  Dec 16, 2011
Printed
Page 108
Topish

declare pipename varchar2(12) := 'signaler'; result integer:= dbms_pipe.create_pipe(pipename); begin dbms_pipe.pack_message('stop'); end; This requires the send message, as so... declare pipename varchar2(12) := 'signaler'; result integer:= dbms_pipe.create_pipe(pipename); begin dbms_pipe.pack_message('stop'); result :=dbms_pipe.send_message(pipename); end;

Note from the Author or Editor:
Change second section of code on page 108 to: DECLARE pipename VARCHAR2 (12) := 'signaler'; result INTEGER := DBMS_PIPE.create_pipe (pipename); BEGIN DBMS_PIPE.pack_message ('stop'); result := DBMS_PIPE.send_message (pipename); END;

Anonymous  Oct 15, 2010  Dec 16, 2011
Printed
Page 131
last code example

The code example refers to the exception name "dynsql.invalid identifier". WHEN dynsql.invalid identifier THEN ... However in the previous declaration section the variable is named "invalid_identifier" (having an underscore between the two words). PRAGMA EXCEPTION_INIT (invalid_identifier, -904);

Note from the Author or Editor:
I have made the fix in ch6 for 6th edition. Simply add an underscore: WHEN dynsql.invalid_identifier THEN ...

Jan Raap  Jul 27, 2012  Oct 12, 2012
Printed
Page 155
2rd program sample.

I think the END LOOP was not at the good place in the sample. code was: LOOP BEGIN UTL_FILE.get_line(l_file, l_line); do_stuff; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; UTL_FILE.fclose(l_file); more_stuff_here; END LOOP; I think it should be: LOOP BEGIN UTL_FILE.get_line(l_file, l_line); do_stuff; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; UTL_FILE.fclose(l_file); more_stuff_here;

Note from the Author or Editor:
I agree. Please make the change as specified.

Martin  Feb 10, 2010  May 06, 2011
PDF
Page 155
2nd paragraph

You’ll find more in the next few sections about what I think you should about this. SHOULD BE: You’ll find more in the next few sections about what I think you should do about this.

Bhavesh  Aug 18, 2012  Oct 12, 2012
Printed
Page 192
192 3rd paragraph, and 193 3rd paragraph

In 10g and more it is 4 bytes by CHAR. I could not said for version before. (The book said 3 bytes) > CREATE TABLE my_strings(vb100 VARCHAR2(100 BYTE), vc100 VARCHAR2(100 CHAR), cb100 CHAR(100 BYTE), cc100 CHAR(100 CHAR)); > COLUMN data_type FORMAT A20; > SET LINE 300 > SELECT column_name,data_type,data_length, char_col_decl_length,char_length,char_used FROM user_tab_columns WHERE table_name='MY_STRINGS'; COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C ----------- --------- ----------- -------------------- ----------- - VB100 VARCHAR2 100 100 100 B VC100 VARCHAR2 400 400 100 C CB100 CHAR 100 100 100 B CC100 CHAR 400 400 100 C

Note from the Author or Editor:
change "three bytes" to "four bytes"

Martin  Feb 12, 2010  Oct 12, 2012
Printed
Page 197
Explanation next to trap icon

ASCII carriage return is CHR(13) not CHR(12) which is form feed.

Note from the Author or Editor:
Change CHR(12) to CHR(13) on 197.

Chris Hanes  Aug 09, 2010  Dec 16, 2011
Printed
Page 197
centre

Windows newline is described as CHR(12)||CHR(10) It should be CHR(13)||CHR(10)

Note from the Author or Editor:
Windows newline is described as CHR(12)||CHR(10) It should be CHR(13)||CHR(10)

Andrew Howell  Apr 20, 2011  Dec 16, 2011
Printed
Page 205
end of the box

The 3 lines of the sample at end of the box should be outside of the box. And if possible probably before the box with the rest of the code of that sample. Also this box could be completely before the Padding section since was related to previous subject.

Note from the Author or Editor:
The last three lines (code) in the bottom of the box on page 205 should be moved to the previous page BEFORE the box starts.

Martin  Feb 15, 2010  May 06, 2011
Printed
Page 209
general syntax of functions.

Small typo: '[' not match ']' in general syntax of: Page 209 REGEXP_INSTR (need to remove ']'), Page 212 REGEXP_SUBSTR (need to remove ']'), Page 214 REGEXP_REPLACE (miss one ']'). Page 227 SOUNDEX('SMYTHE') (have an extra apostrophe in the result).

Note from the Author or Editor:
Correct. Remove one closing square bracket from the general syntax of each of these, as in: ]]]]]]) should be ]]]]])

Martin  Feb 17, 2010  May 06, 2011
Printed
Page 238
sample

In DBMS_OUTPUT.PUT_LINE the text was '100/49' and '100/50' should be 49/100 and 50/100. Samething in the output of that sample.

Note from the Author or Editor:
Please change all the 100/N on the page to N/100 as in 49/100 instead of 100/49.

Martin  Feb 17, 2010  May 06, 2011
Printed
Page 240
sample

On Oracle 11.1.0.7 Linux 32 bit with the file downloaded. I got overflow in the four cases. ERROR at line 1: ORA-01426: numeric overflow That was probably due to implicit convertion (The loop index was probably also a PLS_INTEGER). May you have a 64 bits system so integer have a big range. By changing the loop by: --pls_test: cnt PLS_INTEGER := 1; LOOP int1 := int1 + int2 * cnt; cnt := cnt + 1; EXIT WHEN cnt>iterations; END LOOP; --simple_test: cnt SIMPLE_INTEGER := 1; LOOP int1 := int1 + int2 * cnt; cnt := cnt + 1; EXIT WHEN cnt>iterations; END LOOP; pls_test still have exception. simple_test result was: 123456789 iterations had run time of:+000000000 00:00:07.693335000 123456789 iterations had run time of:+000000000 00:00:00.711185000 So native of 11 times faster for SIMPLE_INTEGER. (+1081% faster) By changing loop again to avoid overflow. int1 := int1 + int2 * cnt / cnt; output: 123456789 iterations had run time of:+000000000 00:01:15.499661000 123456789 iterations had run time of:+000000000 00:01:21.833653000 123456789 iterations had run time of:+000000000 00:00:59.324734000 123456789 iterations had run time of:+000000000 00:00:56.912449000 In my case PLS_INTEGER was faster that SIMPLE_INTEGER in 'interpreted' But PLS_INTEGER was slower that SIMPLE_INTEGER in 'native'. Native was always faster that interpreted.

Note from the Author or Editor:
Agree that the file simple_integer_demo.sql does not run as supplied in the sample file. This will have an impact both on the text of pp 239-240 and on the examples. In fact, I believe it would be better to replace the lines that read int1 := int1 + int2 * cnt; with int1 := int1 + int2; because otherwise the perf test is muddied by the cnt variable, which I believe is defined by the language to be PLS_INTEGER, causing Oracle to do some implicit conversion(s). In the above case, on my machine I get the following: 123456789 iterations had run time of:+000000000 00:00:07.026167000 123456789 iterations had run time of:+000000000 00:00:06.750064000 123456789 iterations had run time of:+000000000 00:00:01.766409000 123456789 iterations had run time of:+000000000 00:00:00.725167000 In any event, we definitely need to amend the example. SF - for OPP6 I will remove the / cnt, and put in the timings above.

Martin  Feb 17, 2010  Oct 12, 2012
Printed
Page 246
1st paragraph

The SIMPLE_FLOAT and SIMPLE_DOUBLE type state that the SIMPLE versions have several more caveats than then their respective basetype: - no check for overflow - unable to use IEEE predicates - unable to use IEEE literals However I can't find this behaviour in the Oracle PL/SQL Reference Guide Furthermore I can't reproduce it: declare l_sf simple_float :=3.40282340E+38F; l_bf binary_float :=3.40282340E+38F; begin dbms_output.put_line('--test binary:'||l_bf); for x in 1..10000 loop l_bf:=l_bf + 1.0E+38f; end loop; dbms_output.put_line(l_bf); if l_bf is infinite then dbms_output.put_line('true'); end if; l_bf := binary_float_nan; if l_bf is NAN then dbms_output.put_line('true'); end if; dbms_output.put_line('--test simple:'||l_sf); for x in 1..10000 loop l_sf:=l_sf + 1.0E+38f; end loop; dbms_output.put_line(l_sf); if l_sf is infinite then dbms_output.put_line('true'); end if; l_sf := binary_float_nan; if l_sf is NAN then dbms_output.put_line('true'); end if; end; OUTPUT: --test binary:3.40282347E+038 Inf true true --test simple:3.40282347E+038 Inf true true Would you care to explain, how you come to your conclusion. What am I missing?

Note from the Author or Editor:
REPLACE THIS TEXT: These datatypes are performance-enhanced versions of the BINARY_FLOAT and BINARY_DOUBLE datatypes—but they do have even more caveats than the SIMPLE_INTEGER type. The SIMPLE_FLOAT and SIMPLE_DOUBLE datatypes have the same range of values as BINARY_FLOAT and BINARY_DOUBLE, but they do not support NULL values, the special IEEE literals (BINARY_FLOAT_NAN, BINARY_DOUBLE_INFINITY, etc.), nor the special IEEE predicates (IS NAN, IS INFINITY, etc.). They also do not check for overflow conditions. WITH: These datatypes are performance-enhanced versions of the BINARY_FLOAT and BINARY_DOUBLE datatypes—but they do not support NULL values.

Geert  Apr 09, 2010  Oct 12, 2012
Printed
Page 262
ACOS, ASIN, ATAN, ATAN2 description

ACON result was between 0 and pi. ASIN result was between -pi/2 and pi/2. ATAN result was between -pi/2 and pi/2. ATAN2 result was between -pi and pi.

Note from the Author or Editor:
On page 262, you will see the following: -pi and -pi (the pi symbol) -pi/2 and -pi/2 in several places. The second "pi" or pi/2 should NOT have a negative sign in front of it. And the entry for ACOS should be between 0 and pi, not 0 and -pi.

Martin  Feb 18, 2010  May 06, 2011
Printed
Page 269
figure

Yes, There are some error in the picture. It is normal that the first three time was the same every where. Yes, Arrow of Selva should be others ways and could have the same text explaination that Donna for clarity. Selva 4th timestamp should be 2009-02-06 01:30:00.00 Database time 4th timestamp should be 2009-02-05 20:00:00.00

Note from the Author or Editor:
In Fig 10-1 there are four errors. 1. Change the direction on the arrows for Sevla. 2. Change Selva 4th timestamp to be 2009-02-06 01:30:00.00 3. Change database fourth timestamp to be 2009-02-05 20:00:00.00 4. Change the string TIMESTAMP WITH TIME LOCAL ZONE to be TIMESTAMP WITH LOCAL TIME ZONE

Martin  Feb 19, 2010  Oct 12, 2012
Printed
Page 280
explanation of paramaters

a description of "string_in" parameter is given. However all previous syntax use "string" as parameter

Note from the Author or Editor:
Change "string_in" under the line For all other cases the parameters are as follows to simply "string" (without the double quotes).

Geert  Feb 24, 2010  May 06, 2011
PDF
Page 323
insert statement just after 2nd paragraph

insert statement with record mentioned in book as - INSERT INTO rain_forest_history (country_code, analysis_date, size_in_acres, species_lost) VALUES rain_forest_rec; BUT while using records for inserting into a table; column names should not be used as otherwise it will throw error - ORA -00947: not enough values.

Note from the Author or Editor:
You are correct. This will be changed in the 6th edition. The fix is simply to remove that second line of code so we just have INSERT... VALUES...

prashant sharma  Nov 13, 2012 
Printed
Page 325
3th code sample

missing ) at end.

Note from the Author or Editor:
chagne this second_book.favorite_author IS NULL) to second_book.favorite_author IS NULL))

martin  May 07, 2010  May 06, 2011
Printed
Page 333
First row of the table (Section A)

The sentence "By taking this approach, I can declare nested tables in any PL/SQL block that has SELECT authority on the type" seems incorrect: 1. Only EXECUTE privilege, not SELECT, is applicable to TYPEs. 2. "block that has ... authority" - should not it be something like "block whose owner has ... authority" ?

Note from the Author or Editor:
Both correct. Change sentence to: By taking this approach, I can use the type to declare nested tables from within any schema that has EXECUTE authority on the type.

Anonymous  Sep 24, 2011  Dec 16, 2011
Printed
Page 337
Definition of function true_colors

The BULK COLLECT clause is missing in the statement: SELECT favourite_colors INTO l_colors ...

Note from the Author or Editor:
change this SELECT favorite_colors INTO l_colors to SELECT favorite_colors BULK COLLECT INTO l_colors

Gustavo  Apr 23, 2010  May 06, 2011
Printed
Page 338
2th code sample

Inserted values was not the same as Table 12-1. id 42 instead of 10010. dependent_birtdate_t 12-jan-1765 instead 12-jan-1763. :)

Note from the Author or Editor:
change this VALUES (42, to VALUES (10010, change this dependent_birthdate_t( '12-JAN-1765 to dependent_birthdate_t( '12-JAN-1763

martin  May 07, 2010  May 06, 2011
Printed
Page 341
Table 12-2, Referenced via

In PL/SQL element, referenced via: Associative Array: Should be VARCHAR2 and BINARY_INTEGER. Nested Table: I don't think you could use VARCHAR2. Should be BINARY_INTEGER or subtype probably. You could also add in table: If you want to search in your collection by VARCHAR2 key to use Associative Array.

Note from the Author or Editor:
Make the following changes: 1. Change "In PL/SQL element, referenced via:" in first column to Index type 2. Change entry under Associative Array to: BINARY_INTEGER (and any of its subtypes) or VARCHAR2 2. Change entry under Nested Table to: Positive integer between 1 and 2,147,483,647

Martin  Feb 24, 2010  May 06, 2011
Printed
Page 346
The EXTEND Method. Boundary Considerations.

EXTEND will not skip the TRIMed element when it assigns a new index. It will initialize TRIMed element again.

Note from the Author or Editor:
In Boundary considerations section on page 346, remove the first sentence.

Kostya  Nov 03, 2010  Dec 16, 2011
Printed
Page 346
The EXTEND Method: under "Exceptions possible"

"... raises the SUBSCRIPT_BEYOND_LIMIT exception." For correctness, as well as consistency with other "Exceptions possible" paragraphs, should be: "... raises the SUBSCRIPT_OUTSIDE_LIMIT predefined exception." Proof: 1. From the standard package: COLLECTION_IS_NULL exception; pragma EXCEPTION_INIT(COLLECTION_IS_NULL , '-6531'); SUBSCRIPT_OUTSIDE_LIMIT exception; pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-6532'); SUBSCRIPT_BEYOND_COUNT exception; pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-6533'); 2. Try these: exec RAISE SUBSCRIPT_BEYOND_LIMIT exec RAISE SUBSCRIPT_OUTSIDE_LIMIT -- Note the ORA-06532 error, and compare with the pragma, above. DECLARE TYPE vat IS VARRAY(2) OF PLS_INTEGER; l_vat vat := vat(1,2); BEGIN l_vat.EXTEND; END; /

Eric Levin  Aug 18, 2012  Oct 12, 2012
Printed
Page 347
1st sample

missing the := at end of the third line.

Note from the Author or Editor:
change this companies ((companies.COUNT) + 1).company_id company_rec.company_id; to companies (companies.COUNT + 1).company_id := company_rec.company_id;

Martin  May 07, 2010  May 06, 2011
Printed
Page 359
6th paragraph

should not have ( after -2 exposant 31

Note from the Author or Editor:
change (and 2 to and 2

martin  May 07, 2010  May 06, 2011
Printed
Page 372
bidir.pkg definition

References to employee table should be to employees table. For instance, instead of 4 FUNCTION rowforid (id_in IN employee.employee_id%TYPE) 5 RETURN employee%ROWTYPE; the function definition should be 4 FUNCTION rowforid (id_in IN employees.employee_id%TYPE) 5 RETURN employees%ROWTYPE; There is another employee%ROWTYPE farther down that also needs to change, and several in the package body. It looks like the sample code may have been partially corrected, but the %ROWTYPE errors are still there. Without these changes, execution returns errors.

Note from the Author or Editor:
Agreed, change all refs to employee table to employees. Will update for OPP6.

Peter Buck  Jan 16, 2013 
Printed
Page 373
Bottom of page

Should not the following text: TYPE pet_t is OBJECT read CREATE TYPE pet_t is OBJECT because you can't issue the statement "TYPE xxx IS OBJECT..." in SQL? The file referenced, object_collection.sql, has the correct text.

Note from the Author or Editor:
Correct. I will add CREATE to that and several other statements on that page in OPP6.

Jason Lind  Feb 08, 2013 
PDF
Page 387
Nested table multiset operations section

You're seriously wrong with your same explanations of DISTINCT keyword: "The DISTINCT keyword forces the elimination of duplicates from the returned nested table", as written in your book. I tested on Oracle 11g2 and referenced some documentations from Oracle and led to the conclusion: If you have two nested table x and y, with DISTINCT key word, you will eliminate all the repeated elements in x and in y before doing the operation INTERSECT, EXCEPT, or UNION. One more thing is that you didn't mentioned NULL values in a multiset and its effect.

Note from the Author or Editor:
I have adjusted the text for the 6th edition to address this, by matching the descriptions in the Oracle text. So the last sentence in each of the descriptions are as follows: EXCEPT The DISTINCT keyword instructs Oracle to eliminate any element in x which is also in y, regardless of the number of occurrences. INTERSECT The DISTINCT keyword forces the elimination of duplicates from the returned nested table, including duplicates of NULL, if they exist. UNION The DISTINCT keyword forces the elimination of duplicates from the returned nested table, including duplicates of NULL, if they exist.

Tiep  Jun 26, 2012  Oct 12, 2012
Printed
Page 407
example code

I think it should be explained that the dbms_lob.write only succeeds because the row is locked implicitly by the INSERT statement and that under other circumstances the SELECT statement would need to explicitly lock the row with a FOR UPDATE clause.

Note from the Author or Editor:
p. 408 insert the following just prior to the para beginning "LOB updates take place within the context of a transaction.": Writing to an existing LOB requires a lock on the row containing the LOB value. In this example, the session implicitly obtained the needed lock on INSERT. However, unlike in conventional (non-LOB) updates, if I later need to write to an existing LOB, I'll need to do a SELECT...FOR UPDATE to obtain a lock explicitly. Otherwise Oracle will give a <i>ORA-22920 row containing the LOB value is not locked</i> error.

Anonymous  Oct 28, 2011  Oct 12, 2012
Printed
Page 408
First Paragraph after the example code

'In this example, I used both WRITE and WRITEAPPEND soley to demonstrate the use of both procedures. because my LOB had no data to begin with, I could have done all the work using only WRITEAPPEND. I believe the last 'WRITEAPPEND' should say 'WRITE'

Note from the Author or Editor:
Correct. Change "only WRITEAPPEND" to "only WRITE"

Anonymous  Mar 22, 2010  May 06, 2011
Printed
Page 411
last line of code on the page

The statement: web_page := BFILENMAE('BFILE_DATA','Tannery_Falls.htm'); should be: web_page := BFILENMAE('BFILE_DATA','TanneryFalls.htm'); to match the file name in the code download zip file. In addition, the download ZIP file has the following issues: 1. The TanneryFalls.htm HTML refers to a "Tannery Falls_files" directory instead of "TanneryFalls_files", and 2. The TanneryFalls_files directory is empty. The following files should be moved into it: DCP_1486.jpg DCP_1489.jpg Dcp_1505.jpg Dcp_1510.jpg Dcp_1519.jpg Dcp_1545.jpg Dcp_1546.jpg general_style.css site_logo.gif

Note from the Author or Editor:
The statement: web_page := BFILENMAE('BFILE_DATA','Tannery_Falls.htm'); should be: web_page := BFILENMAE('BFILE_DATA','TanneryFalls.htm');

Emil Gottwald  Aug 31, 2011  Dec 16, 2011
Printed
Page 413
Last line of code on the page

The statement: VALUES ('Tannery Falls', EMPTY_CLOB ()); should be: VALUES ('Tannery Falls', EMPTY_CLOB ()); i.e., one space between 'Tannery' and 'Falls'. The download code is correct. Ditto for the comment: -- Delete row for Tannery Falls, so this example

Note from the Author or Editor:
Agreed, please remove the extra space.

Emil Gottwald  Aug 31, 2011  Dec 16, 2011
Printed
Page 425
Top box

The statement: WHERE INSTR(falls_directions, 'MACKINAC BRIDGE') <> 0; should be: WHERE INSTR(falls_directions, 'MACKINAC BRIDGE') <> 0; i.e., only one space in the search string.

Note from the Author or Editor:
Agreed. Please remove extra space.

Emil Gottwald  Aug 31, 2011  Dec 16, 2011
Printed
Page 427
First and third INSERT statements

The two spaces should be replaced by a single space in the two <name> values...

Note from the Author or Editor:
Change Munising Falls to Munising Falls Laughing Whitefish to Laughing Whitefish

Emil Gottwald  Aug 31, 2011  Dec 16, 2011
Printed
Page 454
The LOCK TABLE Statement

"The syntax for this statement is: LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];" In Oracle 11g R1, LOCK TABLE has new syntax that lets you specify the maximum number of seconds the statement should wait to obtain a DML lock on the table.

Note from the Author or Editor:
REPLACE LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT]; WITH LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT | WAIT numseconds ] ...AND REPLACE If you leave out the NOWAIT keyword, the database waits until the table is available (and there is no set limit on how long the database will wait). WITH Beginning in Oracle Database 11g Release 1, you can set the number of seconds to wait by using the WAIT clause. If you want to wait forever, leave out both NOWAIT and WAIT.

Zlatko Sirotic  Oct 10, 2009  Oct 12, 2012
Printed
Page 468
the sample

3th line bad text alignment before 'UPDATE call'

Note from the Author or Editor:
Indent this line UPDATE call to that it looks like LOOP UPDATE call

Martin  May 07, 2010  May 06, 2011
Printed
Page 485
2th sample

Same problem 31-dec-2001 should be 01-jan-2002 to not miss last day.

Note from the Author or Editor:
change this in 2 places: AND i.invoice_date BETWEEN '01-JAN-2001' AND '31-DEC-2001'; to AND TO_CHAR (i.invoice_date, 'YYYY') = '2001';

Martin  May 07, 2010  May 06, 2011
Printed
Page 487
5th paragraph

ROWCOUNTM should be ROWCOUNT.

Note from the Author or Editor:
change %ROWCOUNTM) to %ROWCOUNT)

Martin  May 07, 2010  May 06, 2011
Printed
Page 491
2nd code block

CURSOR joke_cur (category_in IN ARCHAR2) missing V in VARCHAR2

Note from the Author or Editor:
Add V to ARCHAR2

Anonymous  Aug 05, 2010  Dec 16, 2011
Printed
Page 494
1st sample

Line 8 missing the FOR UPDATE before ;

Note from the Author or Editor:
change this AND completed_flag = 'NOTYET'; to AND completed_flag = 'NOTYET' FOR UPDATE;

Martin  May 07, 2010  May 06, 2011
Printed
Page 523
1st sample

To have the complete year you should pass 01-jan-83 instead of 31-dec-82 to not miss the last day.

Note from the Author or Editor:
chagne this TO_CHAR ('01-jan-82', 'DD-MON-RR'), TO_CHAR ('31-dec-82', 'DD-MON-RR') to TO_DATE ('01-01-1982', 'DD-MM-YY'), TO_DATE ('31-12-1982 24:59:59', 'DD-MM-YY HH24:MI:SS')

Martin  May 07, 2010  May 06, 2011
Printed
Page 523
2nd paragraph

On your errata page for this site, you have this "correction": ---------------------------------------------------- Note from the Author or Editor: chagne this TO_CHAR ('01-jan-82', 'DD-MON-RR'), TO_CHAR ('31-dec-82', 'DD-MON-RR') to TO_DATE ('01-01-1982', 'DD-MM-YY'), TO_DATE ('31-12-1982 24:59:59', 'DD-MM-YY HH24:MI:SS') ---------------------------------------------------- The correction has mistakes: 1) You need to replace YY with YYYY when using 4-digit years. 2) You need to replace 24 with 23, because the time does not go up to 24:59:59.

Note from the Author or Editor:
Change YY to YYYY Change 24 to 23.

Dan Tillinghast  Jul 07, 2010  Dec 16, 2011
Printed
Page 529
Above code block under Working with Objects and Collections

Text says preexisting_conditions is VARRAY while subsequent code block declares the variable as nested table.

Note from the Author or Editor:
Change and VARRAY type to and nested table type

Chris Hanes  Aug 06, 2010  Dec 16, 2011
Printed
Page 538
last paragraph

The paragraph starts with "Note that I included the word 'usually' here.." Actually the word "usually" is nowhere on the page. Most probably the intended reference is ".. can more likely take advantage" in the prior paragraph.

Note from the Author or Editor:
change word "usually" to phrase "more likely"

Geert  Mar 09, 2010  May 06, 2011
Printed
Page 538
the box of binding vs concatenation

missing one concatenation(||) between tab and 'set sal...' in both column.

Note from the Author or Editor:
change tab 'SET to tab || 'SET

Martin  May 07, 2010  May 06, 2011
Printed
Page 541
in sample

should not have ; at end of line 22 but missing one at end of line 23. END;'; should be END;' USING value1_in, value2_in should be USING value1_in, value2_in;

Note from the Author or Editor:
change this END;'; USING value1_in, value2_in to END;' USING value1_in, value2_in;

Martin  May 07, 2010  May 06, 2011
Printed
Page 542
2nd/3rd paragraph

These paragraphs describe the SQL Guard utility to be found on the books website. Things is this website only includes examples and the 2 final chapters. Is this intentionality or was it forgotten?

Note from the Author or Editor:
I will update the zip to include this file.

Geert  Mar 12, 2010 
Printed
Page 542
2nd Paragraph

I see around March 2010 that a person posted about SQLGuard.zip. The author stated that the zip file will be updated to include the zip. I just purchased the book and downloaded the zip file but to no avail....no SQLGuard.zip yet. Is it named something else or is it still not included?

Note from the Author or Editor:
I will send a version of the file that contains sqlguard.zip.

Anonymous  Jul 26, 2011 
Printed
Page 543
7th paragraph (tip)

You said 4GB for CLOB but I am prety sure you said 8T to 128T before.

Note from the Author or Editor:
Change this CLOB, whose maximum length is 4GB to CLOB.

martin  May 07, 2010  May 06, 2011
Printed
Page 561
Item 1. middle of page

user_cursor should be use_cursor

Note from the Author or Editor:
Change: 1. Create the user_cursor to 1. Create the use_cursor

Chris Hanes  Aug 06, 2010  Dec 16, 2011
Printed
Page 566
4th

1000-line program or five individual 2000-line program you probably want to said: 1000-line program or five individual 200-line program

Note from the Author or Editor:
Correct. Change 2000 to 200

Martin  May 07, 2010  May 06, 2011
Printed
Page 582-583
.

On page 582 of this book, near the top, it says that an OUT parameter is Write-only. This is incorrect; An OUT parameter can be read as well as written to, as a simple test will prove. On the next page, 583, it says "You cannot assign an OUT parameter's value to another variable or even use it in a reassignment to itself." This too is incorrect. You can assign the parameter's value to another variable -- once again, as a simple test will prove. This procedure works: PROCEDURE Parse_HC (p_timestamp_string IN VARCHAR2, p_timestamp_date OUT DATE) AS v_date DATE; BEGIN p_timestamp_date := TO_DATE(p_timestamp_string, 'YYMMDDHH24MI'); v_date := p_timestamp_date; END Parse_HC;

Note from the Author or Editor:
Will be fixed in 6th edition.

Anonymous  Apr 01, 2013 
PDF
Page 585
Under heading - Positional notation and Just below the second paragraph

Paragraph read as - With the following total_sales example, PL/SQL associates the first actual parameter, :order.company_id, with the first formal parameter, company_id_in. It then associates the second actual parameter, N, with the second formal parameter, status_in: new_sales := total_sales (:order.company_id, 'N'); Not understood why ':' is used prefixing, order.company_id? In the next page same under the heading - Named Notation same example is used without ':'. I believe its used by mistake.

Note from the Author or Editor:
Change :order.company_id to l_company_id in both paragraph and code.

prashant sharma  Nov 16, 2012 
PDF
Page 585
Under heading - Positional notation and Just below the second paragraph

Paragraph read as - With the following total_sales example, PL/SQL associates the first actual parameter, :order.company_id, with the first formal parameter, company_id_in. It then associates the second actual parameter, N, with the second formal parameter, status_in: new_sales := total_sales (:order.company_id, 'N'); Not understood why ':' is used prefixing, order.company_id? In the next page same under the heading - Named Notation same example is used without ':'. I believe its used by mistake.

Note from the Author or Editor:
Change :order_company_id to l_company_id in both the paragraph and the code.

prashant sharma  Nov 16, 2012 
Printed
Page 604
code sample

In the body of "total_sales" function, CLOSE tot_cur; is missing

Note from the Author or Editor:
Please insert after FETCH and before RETURN line, at same indentation: CLOSE tot_cur;

Yongsik Kim  Feb 27, 2011  May 06, 2011
PDF
Page 609
Statement after 3 paragraph(excluding code)

Paragraph read as - I would like to return a nested table based on the table definition itself. That is, I would like it to be defined as follows: TYPE tickertype_nt IS TABLE of tickertype%ROWTYPE; I believe author(Steve) intended to write it as - TYPE tickertype_nt IS TABLE of tickertable%ROWTYPE;

Note from the Author or Editor:
Correct! PLease change this line: TYPE tickertype_nt IS TABLE of tickertype%ROWTYPE; to TYPE tickertype_nt IS TABLE of tickertable%ROWTYPE;

prashant sharma  Nov 17, 2012 
PDF
Page 610
Definition of FUNCTION stockpivot

In the definition of FUNCTION stockpivot one line comment symbol - '--' appears twice without any commenting sentence. I believe author want to include these comments as follows- 1) First comment should replace -- to -- first row 2) Second Comment should replace -- to --Second row

Note from the Author or Editor:
Change the first -- to -- Create the OPEN object type instance and change the second -- to -- Create the CLOSED object type instance

Prashant Sharma  Nov 17, 2012 
Printed
Page 611
7th item in table

The description for lines 21-25 should start 'Use the "close" data ...', rather than repeating 'Use the "open" data ...'.

Note from the Author or Editor:
Change second "open" on page to "closed". Fixed for 6th edition.

Tony Dolton  Aug 02, 2012  Oct 12, 2012
PDF
Page 637
In code just below 1st paragraph under heading - Working with packaged cursors

Code written in book as - DECLARE onebook book_info.bytitle_cur%ROWTYPE; BEGIN OPEN book_info.bytitle_cur ('%PL/SQL%'); LOOP EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; book_info.display (onebook); END LOOP; CLOSE book_info.bytitle_cur; END; In the above code - 1) Is not it a good practice to have Fetch first and then exit statement in the loop? For Example, lets say there is only one record retreived by the cursor then it will be displayed twice as fetching no rows in second attempt(or when the cursor pointer move past the last row ) is not going to nullify the last record values fetched. I believe author not meant to print the last record twice! 2) Other way is to have first fetch statement just after the opening of cursor before loop. This is just my view it depends upon author, how he want to correct it.

Note from the Author or Editor:
Move the FETCH line above the EXIT line in the block of code referenced by the reader.

Prashant Sharma  Nov 19, 2012 
PDF
Page 652
Second paragraph after heading - DML Triggers

Paragraph in book read as - There are many options regarding DML triggers. They can fire after or before a DML statement, or they can fire after or before each row is processed within a statement. They can fire for INSERT, UPDATE, MERGE, or DELETE statements, or combinations of these three. ---- Last line ...Combination of three might not be very clear to readers new to PL SQL programming/ triggers. It will be more clear if it reads like - ...combination of these three - Triggering event(insert, update etc.), triggering time(before, after) and triggering level (for each row). This is just a suggestion for improved reading experience.

Note from the Author or Editor:
I will clean this up in 6th edition; no changed needed in 5.

Prashant Sharma  Nov 20, 2012 
PDF
Page 654
DML trigger scripts table

In Book - Under description column last sequence of sql statements- INSERT INTO to_table VALUES (1); UPDATE to_table SET col1 10; DELETE to_table; --- it should be SET col1 = 10; '=' missing.

Note from the Author or Editor:
change SET col1 10 to SET col1 = 10

Prashant Sharma  Nov 20, 2012 
PDF
Page 654
First point under heading - Transaction participation

n Book - If a trigger raises an exception, that part of the transaction is rolled back. -- To be precise it should be - If a trigger raises an un-handeled exception, that part of the transaction is rolled back. Please consider following below code for your reference - 1) create table test_123( n number); 2) create or replace trigger trig_test_123_after_insert after insert on test_123 for each row begin raise_application_error(-20005,'delibrate'); end; 3) begin insert into test_123 values (123); exception when others then null; end; select * from test_123; ---- Other scenario 4) create or replace trigger trig_test_123_after_insert after insert on test_123 for each row begin raise_application_error(-20005,'delibrate'); exception when others then null; end; 5) begin insert into test_123 values (123); exception when others then null; end; select * from test_123;

Note from the Author or Editor:
Very minor. Please change the first bullet point at bottom of page to: If a trigger terminates with an unhandled exception, then the statement that caused the trigger to fire is rolled back.

Prashant Sharma  Nov 20, 2012 
Printed
Page 666
1st sample

line 5 and 6: ELSE 'over' || ' Par' END; by ELSE 'over' END || ' Par';

Note from the Author or Editor:
Correct. The END keyword should be moved ahead of the concatenation to ' Par'

martin  May 07, 2010  May 06, 2011
Printed
Page 669
1st sample

text before said: next-highest salary in his department. but code take the highest of the whole compagnies... miss probably a WHERE clause like dep=:new.dep

Note from the Author or Editor:
1. change ON employee to ON employees 2. Change FROM employee to FROM employees 3. under FROM employees add WHERE clause so it looks like: FROM employees WHERE department_id = :NEW.department_id;

Martin  May 07, 2010  May 06, 2011
Printed
Page 690
last block of code

INTEAD should be INSTEAD

Note from the Author or Editor:
Correct please change

martin  May 07, 2010  May 06, 2011
Printed
Page 703
4th example

the second SET_SESSION_TIMEOUT should be SET_TIMEOUT. The sample code is correct.

Note from the Author or Editor:
WIll apply to OPP6.

Peter Buck  Jan 25, 2013 
Printed
Page 730

alter session comand is missing = sign.

Note from the Author or Editor:
change MODE SIGNATURE to MODE=SIGNATURE

Chris Hanes  Jun 22, 2011  Dec 16, 2011
Printed
Page 761
table

Table contains a description of QEM tracing options a few errors occur :) totable: "..to the the.." double occurrence of "the" toscreen: wrong explanation (copied from totable)

Note from the Author or Editor:
* Remove the duplicate word as indicated. * Change description for toscreen to: Redirect trace output to the screen.

Geert  Mar 29, 2010  May 06, 2011
Printed
Page 761
In box (toscreen)

was the same decription of above. Should be probably to screen.

Note from the Author or Editor:
The description for toscreen should be Directs output to the screen.

martin  May 07, 2010  May 06, 2011
Printed
Page 778
5th paragraph

The paragraph references to "11GR2_editions.sql" file on the books website. However the file isn't available within the zip file on the website

Note from the Author or Editor:
correct. I will provide an updated zip file.

geert  Mar 29, 2010 
Printed
Page 779
code snippet below 1st paragraph

Code for creation an editions is wrong: CREATE_EDITION_NEW_HR_PATCH_NAMEFORMAT / should be CREATE EDITION NEW_HR_PATCH_NAMEFORMAT; /

Geert  Mar 29, 2010  May 06, 2011
Printed
Page 808
3rd example (near bottom)

FUNCTION FUNCTION session_constant RETURN VARCHAR2 --should be-- FUNCTION session_constant RETURN VARCHAR2

Note from the Author or Editor:
Agreed, will fix for OPP6. Remove the first FUNCTION word.

Peter Buck  Jan 29, 2013 
Printed
Page 822
1st sample

names and mileage collection near to be initialized before EXTEND. You could also remove the space between 'major_polluters_cur' and '%ROWCOUNT' but that not seem to cause compile error.

Note from the Author or Editor:
change these lines: names names_t; mileages mileage_t; to names names_t := names_t(); mileages mileage_t := mileage_t(); and change this major_polluters_cur %ROWCOUNT to major_polluters_cur%ROWCOUNT

Martin  May 14, 2010  May 06, 2011
Printed
Page 827
top of page

Book uses varray: names name_varray; old_salaries number_varray; new_salaries number_varray; Example code (onlyfair.sql) uses nested table: names name_tab; old_salaries number_tab; new_salaries number_tab;

Note from the Author or Editor:
Change "_varray" to "_tab" on the page.

Bob  Jan 13, 2011  Dec 16, 2011
Printed
Page 834
1st sample and is output

First, line 23 not appear in the output below. Instead of EXEC bulk_exceptions should be / Message 1 should be iteration 3 instead of 2 and message should be cannot update to NULL. Message 2 should be iteration 5 instead of 4 and message value to large. PS.: Message seem to be in inverse order with wrong iteration number.

Note from the Author or Editor:
Please change the four lines under SQL> EXEC to this: Error 1 occurred during iteration 3 updating name to BIGBIGGERBIGGEST Oracle error is ORA-01407: cannot update () to NULL Error 2 occurred during iteration 5 updating name to Oracle error is ORA-01401: inserted value too large for column

Martin  May 17, 2010  May 06, 2011
Printed
Page 837
2th paragraph

Instead of line 19(this was a blank line) it should be line 20.

Note from the Author or Editor:
Change (line 19) to (line 20)

martin  May 17, 2010  May 06, 2011
Printed
Page 881
2/3 way down page

"If you try to open with append, the file must already exist...if not..INVALID_OPERATION will be raised". Oracle documentation: "If you try to open a file specifying 'a' or 'ab' for open_mode but the file does not exist, the file is created in write mode." (http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_file.htm#i1003326) I tried it, opening a non-existent file with either W or A is fine. Perhaps I'm missing something, great book though, cheers.

Note from the Author or Editor:
Confirmed and fixed in 6th edition

Jason  Jun 26, 2013 
Printed
Page 890
2th paragraph

UTL_FILE.RENAME should be UTL_FILE.FRENAME

Note from the Author or Editor:
UTL_FILE.RENAME should be UTL_FILE.FRENAME

Martin  Jun 01, 2010  Dec 16, 2011
Printed
Page 933
2nd paragraph

"There are essentially three different types of key management. • A single key for the entire database. • A single key for each row of tables with encrypted data. • A combination of the above two." There are essentially four different types of key management. • A single key for the entire database. • A single key for EACH TABLE with encrypted data. • A single key for each row of tables with encrypted data. • A combination of the above ... Oracle Database Advanced Security Administrator's Guide (11.2), page 3-2: TDE Column Encryption ...This master encryption key is used to encrypt THE TABLE KEY, which in turn is used to encrypt and decrypt data in the table column...

Note from the Author or Editor:
In addition to the possibility of using a table-level encryption key, it is also possible to use a column-level key. Adding these two extra approaches would require some extra paragraphs. SF - I have sent this errata to Arup, who "owns" this chapter. It will be addressed in OPP6

Zlatko Sirotic  Oct 31, 2009 
Printed
Page 941
1st paragraph

key "1234567890123456" should be instead "SecretKey" and "Another Key".

Note from the Author or Editor:
REPLACE I will make it secure by adding a key—“1234567890123456”. WITH I will make it secure by adding a key—“SecretKey”. Then I will compute second MAC value using another key.

Martin  Jun 04, 2010  Oct 12, 2012
Printed
Page 944
Transparent Tablespace Encryption

"To address these drawbacks, Oracle Database 11g has introduced a new feature: Transparent Tablespace Encryption (TTE)." Oracle Database Advanced Security Administrator's Guide (11.2), page 3-2, uses terms "TDE (Transparent Data Encryption) Column Encryption" and "TDE Tablespace Encryption".

Note from the Author or Editor:
Oracle uses both terms, but it appears that TDE Tablespace Encryption is more common. REPLACE Transparent Tablespace Encryption (TTE). With TTE, WITH TDE Tablespace Encryption. With this feature, And then on p 944, replace occurrences of TTE with TDE tablespace encryption

Zlatko Sirotic  Oct 31, 2009  Oct 12, 2012
Printed
Page 956
1st sample end of line 22

At end of line 22 there number 23 that is the number for the next line.

Note from the Author or Editor:
Change SENSITIVE23 to SENSITIVE and put "23" on the left as the line number.

Martin  Jun 07, 2010  Dec 16, 2011
Printed
Page 962
2th and 5th paragraph

About RLS with SQL*Loader's Direct Path Load/Direct Path Insert. The second paragraph(and 7th bullet on the next page) seem to said it was bybass but the 5th seem to said it will raise error. It depend on the DB version?

Note from the Author or Editor:
REPLACE THIS TEXT: you need to be aware that the RLS policies on affected tables will not be invoked. After all, the point of these direct path operations is to bypass the SQL layer. You will have to take special precautions to deal with this situation. WITH: you will have to take special precautions to achieve the desired result. That is because direct path operations attempt to bypass the SQL layer, but RLS needs the SQL layer to operate correctly. REPLACE THIS TEXT: When a table is loaded via a direct path operation, the SQL layer is bypassed, and the RLS policy cannot be enforced, resulting in an error. WITH: Direct path operations do not work in combination with RLS. Oracle will force direct path exports to run in conventional mode, but imports & inserts will fail.

Martin  Jun 07, 2010  Oct 12, 2012
Printed
Page 975
1-st

Instead of: Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, policy_name: should be, I think: Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, statement_types: (i.e. replace policy_name with statement_types)

Note from the Author or Editor:
Instead of: Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, policy_name: should be, I think: Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, statement_types: (i.e. replace policy_name with statement_types)

M.R.  Apr 05, 2011  Dec 16, 2011
Printed
Page 979
1st sample result

15000 should be 150000 and the size probably 6 instead of 5.

Note from the Author or Editor:
Change (5):15000 to (6):150000

Martin  Jun 08, 2010  Dec 16, 2011
Printed
Page 1002
middle

$ENDIF should be $END

Note from the Author or Editor:
Change as directed.

Anonymous  Jun 17, 2012  Oct 12, 2012
PDF
Page 1049
near the end

I may be not a plsql guru, but i'm native in spanish. The following sentence is not correct in that there is no longer a "double l" letter in the Spanish alphabet, according to the Royal Spanish Language Academy. In fact, apart from the "n~" letter, the Spanish alphabet is idem to the English one. This change was introduced recently, i guess the nineties, so it's highly probable that legacy books may still list it as a separate letter, along with the "ch", also gone. "Spanish, for example, combines two “l”s to form a new character that is pronounced differently and that comes after the letter “l” in the Spanish alphabet." The point is, that an English-type sort will be considered correct by a Spanish speaker. In case you wanna check: http://en.wikipedia.org/wiki/Spanish_orthography

Note from the Author or Editor:
Remove the bullet item starting with "Concatentaed characters...:"

Anonymous  Apr 09, 2012  Oct 12, 2012
Printed
Page 1060
2nd paragraph, after the 1st list of dates/times returned

The variables returned are labelled as 'TIMEZONE' and 'TIMEZONE WITH LOCAL TIMESTAMP',while they should be 'TIMESTAMP' and 'TIMESTAMP WITH LOCAL TIMEZONE'. It's just a typo, but even if the correct sentence can be inferred from the context it's nonetheless an unpleasant thing to see in such a prestigious book, worth every penny.

Note from the Author or Editor:
Correct and will be fixed in OPP6. The paragraph starting with TIMEZONE and TIMEZONE WITH should be TIMESTAMP and TIMESTAMP WITH

Antonio Regoli  Feb 07, 2013 
Printed
Page 1066
2th Section Title

In second section title: UTL_118N should be UTL_I18N (the first '1' should be 'I')

Note from the Author or Editor:
Agree. This change needs to get propagated to the TOC as well.

Martin  Jun 28, 2010  Oct 12, 2012
Printed
Page 1075
Table 26-1. Significant object programming features in the Oracle database

"'Private' attributes, variables, constants, and methods - in 11g" Oracle 11g supports neither private attributes nor private methods. (See page 1116: However, because Oracle supports neither private attributes nor private methods, the incremental sacrifice here is small.)

Note from the Author or Editor:
Remove the check for "Private" under 11g.

Zlatko Sirotic  Oct 10, 2009  Dec 16, 2011
Printed
Page 1085
foodstuffs_nt examples

In line: 'FOR indx IN fridge_1 .. fridge_contents.C' The 'fridge_1' should be only '1' or 'fridge_contents.FIRST' if you use the second option, will be better to check for NULL and EMPTY before. The 'fridge_contents.C' should be 'fridge_contents.COUNT' or 'fridge_contents.LAST' if you use the second option, will be better to check for NULL and EMPTY before.

Note from the Author or Editor:
Change the FOR indx line to; FOR indx in 1 .. fridge_contents.COUNT

Martin  Jun 29, 2010  Dec 16, 2011
Printed
Page 1095
DROP TYPE ...

"By the way, you can drop a type using the statement: DROP TYPE typename [ FORCE ]; Use the FORCE option (available only in Oracle Database 11g Release 2)..." "DROP TYPE typename FORCE" exists even in 8.0. New feature in Oracle Database 11g Release 2 is this: CREATE OR REPLACE TYPE statements now have a FORCE option, which enables you to replace the type even if it has type dependents.

Note from the Author or Editor:
Delete the text: (available only in Oracle Database 11g Release 2)

Zlatko Sirotic  Oct 31, 2009  Oct 12, 2012
Printed
Page 1177
Index - R

RAISE_EXCEPTION_ERROR should be RAISE_APPLICATION_ERROR.

Note from the Author or Editor:
Please change RAISE_EXCEPTION_ERROR to RAISE_APPLICATION_ERROR.

Graeme Hewson  Oct 29, 2009  May 06, 2011
Printed
Page 1177
index entry for RAISE_APPLICATION_ERROR

The index entry for RAISE_APPLICATION_ERROR says RAISE_EXCEPTION_ERROR instead.

Note from the Author or Editor:
Change index entry from RAISE_EXCEPTION_ERROR to RAISE_APPLICATION_ERROR

Gustavo  Jun 11, 2010  Oct 12, 2012