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.

The following errata were submitted by our customers and have not yet been approved or disproved by the author or editor. They solely represent the opinion of the customer.

Color Key: Serious technical mistake Minor technical mistake Language or formatting error Typo Question Note Update

Version Location Description Submitted by Date submitted
Other Digital Version 13
About the Code

This is for the Kindle e-book format: About the Code All of the code referenced in this book is available from [ URL]oracle-plsql-sixth. The link above, if followed is a Web Page that contains under Publisher Resources: Supplemental Content: [learning O'Reilly URL]0636920024859/ The link for Supplemental Content returns: Not Found The requested URL /0636920024859/ was not found on this server. It appears that a purchaser of this book is not able to locate any of the source code referenced in this book.

Michael O'Brien  May 21, 2021 
PDF Page 71
Chapter 3, The PL/SQL Character set

In the following paragraph: "The string q’hello!’ bears some explanation. The ! is a user-defined delimiter, also introduced in Oracle Database 10g; the leading q and the surrounding single quotes tell the compiler that the ! is the delimiter, and the string represented is simply the word hello." there is an error. The string q'hello!' is not valid. The ! should be present also as initial delimiter. Correct version should be q'!hello!'.

Alberto Faenza  May 15, 2017 
Printed Page 71
4th paragraph

can implicitly convert '01-OCT-198' should be can implicitly convert '01-OCT-1986'

Anonymous  Dec 05, 2017 
PDF Page 73
Chapter 3, Embedding Single Quotes Inside a Literal String

Several examples are including extra space which causes the example not to be valid in the table having Literal (delimiters highlighted). In PDF version the delimiters are not highlighted and all the following literals have extra space inside the example: q' ( There's no business like show business.) ' q' { "Hound of the Baskervilles" } ' q' [ ' ] ' q' !'hello' ! ' q' |'' | ' The correct representation of these example is q'(There's no business like show business.)' q'{"Hound of the Baskervilles"}' q'[']' q'!'hello'!' q'|''|'

Alberto Faenza  May 15, 2017 
PDF Page 174
Code block at the top of the page.

In this code block: (SELECT c1.*,COUNT(*) FROM customer_known_good GROUP BY c1.cust_nbr, MINUS SELECT c2.*, COUNT(*) FROM customer_test c2 GROUP BY c2.cust_nbr, UNION ALL (SELECT c3.*,COUNT(*) FROM customer_test c3 GROUP BY c3.cust_nbr, MINUS SELECT c4.*, COUNT(*) FROM customer_known_good c4 GROUP BY c4.cust_nbr, it is obvious that customer_known_good should be aliased as c1 but it is not shown as being aliased.

Anonymous  Feb 23, 2017 
PDF Page 182
First example of constant declaration

The first example of constant declaration includes TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) This is technically correct and will produce the desired result, but it is not a good practice. Better to use EXTRACT(YEAR FROM SYSDATE)

Anonymous  Feb 25, 2016 
PDF Page 200
next to last paragraph

"For example, the Unicode UTF-8 character set uses up to 4 bytes for some characters; thus, if UTF-8 is your underlying character set, declaring a VARCHAR2 variable with a maximum length of 100 characters is equivalent to declaring the same variable with a maximum length of 300 bytes." Why should the max length be 300 bytes, instead of 400 bytes? This is either an error, or at least would require further clarification.

Michael Wittmann  Sep 23, 2019 
PDF Page 201
12th line

MAX_SQL_STRING_SIZE parameter does not exist; instead it is MAX_STRING_SIZE in Oracle 12c and higher. This error occurs again on page 202.

Michael Wittmann  Sep 23, 2019 
PDF Page 231
"Character functions and CHAR arguments"

The text says: "When a character function returns a character value, that character value is always of type VARCHAR2 (variable length), with the exceptions of UPPER and LOWER." This is not what the Oracle documentation shows. Obviously, INITCAP should be on the list. But there are more exceptions. SUBSTR returns the same data type as its argument (just like UPPER, LOWER and INITCAP). CONCAT returns CHAR if both of its arguments are CHAR. I didn't check all the character functions, but this already shows the text should be changed.

Anonymous  Feb 27, 2016 
PDF Page 232
4th paragraph - COMPOSE(string)

The use of compose in this chapter is incorrect. Following the example here below: COMPOSE(‘a\0303’) will then return '\00E3', which is the Unicode code point (in hexadecimal) for the character ã. does not return '\00E3' but 'a\0303', same as input string. Even correcting the input, the following: COMPOSE('a'||UNISTR('\0303')) returns characters ã and not its Unicode code point.

Alberto Faenza  May 19, 2017 
PDF Page 236
RPAD example

Hi, - the example for RPAD is RPAD('Merry Christmas! ', 25, 'Ho !') The output shown in the book is 'Merry Christmas! Ho! Ho!'. This output is only 24 characters long - there should be one additional space at the very end, like this: 'Merry Christmas! Ho! Ho! '.

Anonymous  Feb 28, 2016 
PDF Page 249, 250
LOOP body within procedure pls_test AND within procedure simple_test

The procedures calculate successive values for int1, eventually resulting in OVERFLOW; please test on your machine to confirm. This makes sense: the final value of int1 is int1 + int2 * (1 + 2 + ... + iterations). The sum is of the order of 1/2 * iterations^2, and with iterations of the order of 10^8, the result is of the order of 10^16, more than allowed for PLS_INTEGER. For my own testing, I changed to int1 = 1 + int2 * cnt, which generates the same number of additions and multiplications. I get execution times similar to yours. An interesting aside: I got the same overflow problem with simple_test and your loop body. You may want to test to confirm. This is likely caused by cnt: the loop counter is PLS_INTEGER, and since it is mixed with int1 and int2, they are converted form SIMPLE to PLS NOT NULL (according to the Oracle documentation). This would be worth a brief note in itself. Can cnt be forced to SIMPLE_INTEGER? If it can, the speed improvement might become much greater - as it is, simple_test saves only the NULL checks, but not the overflow checks.

Anonymous  Feb 22, 2016 
PDF Page 249, 250
Loop body in test procedures

Following up... I just checked the sample code you provide online, there you just have in1 = int1 + int2; this is not what you have in the book (at least the PDF version), there you have ...+ int2 * cnt. I am glad you have that in the PDF, without it I wouldn't have thought about the various issues. In any case, the code and the book should made to agree. Other notes: (1) I changed from a numeric FOR loop to a WHILE loop, and changed CNT to SIMPLE_INTEGER. The further speed improvement is again by a factor of more than 3. (2) With that said, if the computations were much more complicated, the speed improvement wouldn't be as significant. Checking for NULL and overflow take a few clock ticks, just like addition (and multiplication by 2). If each iteration of the loop took many more clock ticks, for example by multiplying very large numbers, the savings from not checking for NULL and overflow would be almost the same, but compared to a much longer time per loop iteration.

Anonymous  Feb 23, 2016 
PDF Page 293
3rd item of bullet point list

The text is saying: Note the case difference on the month abbreviations of the next two examples. You get exactly what you ask for with Oracle date formats! TO_CHAR (SYSDATE, 'MON DDth, YYYY') --> 'FEB 05TH, 1994' TO_CHAR (SYSDATE, 'fmMon DDth, YYYY') --> 'Feb 5TH, 1994' The TH format is an exception to the capitalization rules. Even if you specify lowercase “th” in a format string, the database will use uppercase TH in the output. This is not actually correct. The TH format changes according the formatting of the number which is attached to. i.e.: TO_CHAR (SYSDATE, 'MON ddth, YYYY') --> 'MAY 23rd, 2017' TO_CHAR (SYSDATE, 'MON dDth, YYYY') --> 'MAY 23rd, 2017' TO_CHAR (SYSDATE, 'MON Ddth, YYYY') --> 'MAY 23rd, 2017' In Oracle 12.2, as per my tests, if a letter of the number format to which TH applies is lowercase then the TH format will use.

Alberto Faenza  May 23, 2017 
Printed Page 491
3rd paragraph

The author writes: "In the previous example, I issued the following statement to exit my loop:" followed by an EXIT statement. But the previous example does not include this EXIT statement.

Nat Dunn  May 24, 2019 
PDF Page 517
Code-fragment on page

The query for the CURSOR does not contain a FOR UPDATE clause, even though this example is supposed to show what will happen when a CURSOR with FOR UPDATE issues a premature COMMIT. Indeed, with my debuggable-in-SQLDeveloper-Version of it, the expected error ORA-01002: fetch out of sequence does not appear. Only if I include the FOR UPDATE will the error occur as predicted. Below my code: 1) Testdata: INSERT INTO winterize VALUES(1,'fix door bell',1,'hammer, screwdriver','Y',TO_NUMBER(TO_CHAR (SYSDATE, 'YYYY')),'N','STEVEN'); 2) a debuggable PROCEDURE made out of the anonymous block: CREATE OR REPLACE PROCEDURE cursor_for_update_commit_err IS /* All the jobs in the Fall to prepare for the Winter */ CURSOR fall_jobs_cur IS SELECT task, expected_hours, tools_required, do_it_yourself_flag FROM winterize WHERE year_of_task = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')) AND completed_flag = 'N' FOR UPDATE; --you forgot that, Steven ;-) BEGIN /* For each job fetched by the cursor... */ FOR job_rec IN fall_jobs_cur LOOP IF job_rec.do_it_yourself_flag = 'Y' THEN /* || I have found my next job. Assign it to myself (like someone || else is going to do it!) and then commit the changes. */ UPDATE winterize SET responsible = 'STEVEN' WHERE task = job_rec.task AND year_of_task = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')); COMMIT; END IF; END LOOP; END;

Anonymous  Jun 06, 2016 
Printed Page 1047
Code line description table

Of type DATA Should be Of type DATE

Anonymous  Apr 25, 2020 
PDF Page 1068
Table 24-1

The table doesn't list the DBMS_DB_VERSION.VER_LE_11 constant

Massimo Pasquini  Aug 15, 2016