Errata

Oracle PL/SQL Programming

Errata for Oracle PL/SQL Programming, Sixth Edition

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
PDF
Page xxxiii
1st paragraph

The second sentence says:
You might also want to visit PL/SQL Obsession (Steven
Feuerstein’s PL/SQL portal) at ,, where you will find training materials, code downloads, and more.

In the PDF, the first dot is actually the link to his page, which is -
http://www.toadworld.com/platforms/oracle/w/wiki/8243.plsql-obsession.aspx

Note from the Author or Editor:
Yes, PL/SQL Obsession is now old and will soon be retired. Instead, it would be better to change this to:

You might also want to visit the Oracle PL/SQL Learning Library, at www.oracle.com/oll/plsql, where you will find resources from both Steven and other PL/SQL experts.

Anonymous  Apr 10, 2015  Jan 08, 2016
Printed, PDF
Page 156
First Line

Currently the line reads as:
"example, I trap two parent-child exceptions, −1 and −2292, and then take an action"

The "-2292" is incorrect, it should be "-2291". That is the correct error number (-2292 is raised during a DELETE statement) and agrees with the error used in the code for the add_company() procedure.

Note from the Author or Editor:
Please make the change as directed by this careful reader.

Anonymous  Sep 22, 2014  Jan 08, 2016
PDF
Page 159
middle of the page

The section titled "Standardize Handling of Different Types of Exceptions" starts with "An exception is an exception is an exception? "

Note from the Author or Editor:
Clearly, my florid writing is not helpful here. Please change the question that starts the section titled above to:

Are all exceptions equal?

Massimo Pasquini  Dec 20, 2015  Jan 08, 2016
Printed
Page 189
Paragraph just before Converstion between Datatypes header

Change sentence starting "Be aware..." to

Be aware that a subtype anchored to a database column does not carry over the NOT NULL constraint to the variables it defines.

Steven Feuerstein
Steven Feuerstein
 
Dec 17, 2015  Jan 08, 2016
PDF
Page 231
2n paragraph titled "Character functions and CHAR arguments"

I found this error in the 5th edition and I found out it's present in the 6th edition too.
There's a wrong statement, probably no longer true on Oracle 12c release. It says: "When a character function returns a character value, that value is always of type VARCHAR2 (variable length), with the exceptions of UPPER and LOWER. These functions convert to uppercase and lowercase, respectively, and return CHAR values (fixed length) "

If you query Oracle 12c dictionary you'll see this is not (or no longer) true and the UPPER and LOWER functions receive and return a VARCHAR2 type

-- Query on the DBA_SOUCE view
SELECT S1.LINE, S1.TEXT, S2.TEXT
FROM DBA_SOURCE S1, DBA_SOURCE S2
WHERE S1.OWNER = 'SYS'
AND S1.NAME ='STANDARD' AND S1.type = 'PACKAGE'
AND ( lower(S1.TEXT) LIKE '% upper(%' OR lower(S1.TEXT) LIKE '% lower(%' )
AND S2.OWNER = S1.OWNER
AND S2.NAME = S1.NAME
AND S2.type = S1.type
AND S2.LINE = S1.LINE + 1
ORDER BY S1.LINE;

-- results:

function UPPER(ch VARCHAR2 CHARACTER SET ANY_CS) return VARCHAR2 CHARACTER SET ch%CHARSET;

function LOWER(ch VARCHAR2 CHARACTER SET ANY_CS) return VARCHAR2 CHARACTER SET ch%CHARSET;

function UPPER(ch CLOB CHARACTER SET ANY_CS) return CLOB CHARACTER SET ch%CHARSET;

function LOWER(ch CLOB CHARACTER SET ANY_CS) return CLOB CHARACTER SET ch%CHARSET;

Note from the Author or Editor:
This is on page 231. In the second to last paragraph on page remove last sentence, and remove "with the exceptions of UPPER and LOWER."

Massimo Pasquini  Dec 13, 2015  Jan 08, 2016
PDF
Page 369
middle of the page

The paragraph titled "Changing a nested table of VARRAY characteristics" was probably meant to be "Changing a nested table OR VARRAY characteristics

Note from the Author or Editor:
Agreed. Please change "of" to "or"

Massimo Pasquini  Jan 06, 2016  Jan 08, 2016
PDF
Page 406
Table at the bottom of the page

"<>" and "!= " operators
Compares two nested tables, and returns FALSE if they differ in named type, cardinality, or equality of elements.

In that case they return TRUE

A question to the author: is here the operator "^=" also supported? In case it is, it should be added; in case it's not, it would be worth to point that out.

Note from the Author or Editor:
Change left column under Operation from

<> or !=

to

<> or != or ^=

Change text under Description to:

Returns TRUE if the two nested tables are of the same type, but differ in cardinality or equality of elements. If the nested tables are of different types, Oracle raises "PLS-00306: wrong number or types of arguments in call to '!='"

Massimo Pasquini  Jan 09, 2016 
PDF
Page 466
Just above table 14-1

The author states: "before Oracle opens the first SQL cursor in the session, all the implicit cursor attributes yield NULL. (The exception is %ISOPEN, which returns FALSE.)"

It should be "before Oracle executes the first SQL statement..."

I tried to open a session and print all the cursor attributes on screen, and I noticed exactly what it's reported on the book, but repeating the experiment and executing a ROLLBACK before printing the attributes, the value of %NOTFOUND turned into TRUE.

Note from the Author or Editor:
On page 466 in 1st paragraph after Cursor Attributes.... header, change "or "SELECT INTO" to simply "SELECT".

Change the second paragraph in that same section to:

First, remember that the SQL% attribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of the SQL% attribute is NULL.

Massimo Pasquini  Jan 16, 2016 
PDF
Page 509
second line

I cannot get what the author means by the term "SQL" in "whenever the outermost PL/SQL block ends and control is returned to SQL or some other calling program"

Could it be SQL*Plus ?

Note from the Author or Editor:
Change

"whenever the outermost PL/SQL block ends and control is returned to SQL or some other calling program"

to

"whenever the outermost PL/SQL block ends and control is returned to the host environment"

Massimo Pasquini  Jan 17, 2016 
PDF
Page 516
2nd paragraph

This sentence is misleading
"You can use the FOR UPDATE clause in a SELECT against multiple tables. In this case, rows in a table are locked only if the FOR UPDATE clause references a column in that table"

I suggest to change it into "You can use FOR UPDATE OF clause..."

It turns out to be in contrast with he sentence a few lines below "If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, the database will then lock all identified rows across all tables listed in the FROM clause."

Note from the Author or Editor:
Change

"You can use the FOR UPDATE clause in a SELECT against multiple tables. In this case, rows in a table are locked only if the FOR UPDATE clause references a column in that table"

to

"You can use the FOR UPDATE OF clause in a SELECT against multiple tables. In this case, rows in a table are locked only if the FOR UPDATE clause references a column in that table"

Massimo Pasquini  Jan 17, 2016 
PDF
Page 753
Output script

Hi Steven:
Output shown for the psobj.sql gives impression that object_type will be displayed once, but in actual, object_type column will have value for each record irrespective of being used in order by (may be you left it to avoid redundancy for a but obvious thing).
For better readability and technical correctness, I think it should be changed.

--
Prashant

Note from the Author or Editor:
Add this line directly after /* File on web: psobj.sql */

BREAK ON object_type SKIP 1

Prashant Sharma  Oct 21, 2015  Jan 08, 2016