MacLochlainns Weblog

This is migrated from a comment thread of my blog post announcing the availability of the Oracle Database 11g & MySQL 5.6 Developer Handbook. Here’s the seeding code for the examples in the book.

You try to eliminate all errors when you write a book. I think we did a great job on this book and the errors are minimal, but readers bring new insights and rereading what you’ve written brings fresh ideas. This errata includes corrections of mistakes, suggestions for change, and improvements to the base text. If the book is successful, I’m sure they’ll make it into the next edition.

If you’re reading or read the book, please feel free to let me know of any uncaught error or suggestion that you may have. Thanks, Michael

Errata:
(Acknowledgment and corrections to Oracle Database 11g & MySQL 5.6 Developer Handbook)

Change Key:

removed text

added text

Chapter 1

Page 18:

The flow of the first paragraph in the MySQL Database Management System section could be improved if re-written as follows.

The MySQL database management system has three major components. Two are similar to the Oracle database’s data repository and its set of programs, but they’re not exactly the same. Two components, the data repository and its set of programs, are implemented differently on each database. The third component is the work areas and they’re shared in MySQL rather than private like the Oracle database.

Page 19:

The “use” should be “uses” in the first sentence of the second paragraph, as:

Database users with the create table privilege may create tables in one database that uses different engines

Page 21:

MySQL objects are tables or views built from tables; therefore, the first sentence of the second paragraph should read as follows.

All objects in the mysql database are tables or views (where views are stored queries based on one or more tables).

Page 22:

Question #10 shouldn’t have the ending clause, and should appear as:

“True. Sequential log files are maintained by programs in MySQL databases as they are for Oracle databases, notwithstanding the engine of implementation.”

Chapter 2

Page 29:

The GRANT in the sidebar is made to “sample” when it should be to “student” on line 4, as shown below:

SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator
  2  ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
  3  ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
  4  ,     CREATE VIEW TO student;

Page 48:

The comparative was missing from the first sentence of the third paragraph after the TIP.

“Unlike Oracle, you can’t grant roles in MySQL.”

There’s an unnecessary “While” starting the second sentence of the third paragraph following the TIP. The sentence should read:

“While MySQL Workbench gives you the impression that MySQL Server supports roles but it doesn’t.”

Page 49:

The reference to “5.5” should be “5.6” in the last sentence of the fourth paragraph. The sentence should read:

“This appears to be a security hole in MySQL 5.6 that Oracle will certainly fix in subsequent releases.”

Page 51:

An oversight was failing to provide a comprehensive list of the show command. It appears it should follow the help list and precede the Shelling out of the MySQL Monitor Environment section this page.

The show statement has many options. Oddly enough they’re visible by leveraging the help command, like this:

mysql> help SHOW

It displays the following:

Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
 
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
 
like_or_where:
    LIKE 'pattern'
  | WHERE expr
 
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
 
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
 
URL: http://dev.mysql.com/doc/refman/5.5/en/show.html

Page 56:

The sidebar at the bottom of the page oddly inverted the declare and define words and neglected to mention the default null value assignment for session variables, and it should read like:

“Define and declare are two words that give grief to newbies. Let’s qualify what they mean. Define Declare means to give a variable a name and data type. Declare Define means to define declare a variable and assign it a value. Unassigned variables are automatically assigned a null value. Another word for assigning a value is initializing the variable.
  You can’t really define declare a variable in MySQL because it disallows this. You must declare define session variables and they inherit their data type from the value you assign. That’s because SQL and SQL/PSM are strongly typed languages and the semantic adopted doesn’t allow explicit data type assignment.”

Page 61:

The reference to “5.5” should be “5.6” in the next to last sentence of the summary paragraph. The sentence should read:

“… in Oracle Database 11g, but presized return values are used in Oracle MySQL 5.6.”

Chapter 3

Page 67:

The reference to “5.5” should be “5.6” in the NOTE. The sentence should read:

“It’s an awesome security feature that presently doesn’t exist for MySQL 5.6.”

There’s a missing “the” in the last sentence of the last paragraph. The sentence should read:

“You secure this portion of the network barrier by implementing HTTPS (Hypertext Transport Protocol Secure) on your Apache server.”

Chapter 5

Page 104:

The word TABLE is misspelled in the last sentence on this page. The sentence should read:

“They also qualify some rules that govern how you can interact with them during creation and removal with the CREATE TABLE and DROP INDEX statements, respectively.”

Page 110:

The first sentence in the next to last paragraph on this page is missing a qualifer, which cross-references an example where you assign an in-line foreign key constraint on page 150 in Chapter 6. The sentence should read:

“The Oracle database also requires that you add foreign key constraints as out-of-line constraints when creating a table unless you’re willing to accept a system generated constraint name for the foreign key constraint.”

Page 114:

The second paragraph in the Trigger Constraints led the reader to believe triggers can’t support table-level constraints. The beginning of the paragraph should read:

“The Oracle database also requires that you add foreign key constraints as out-of-line constraints when creating a table Database triggers can also let you perform table-level behaviors with statement-level triggers. Row-level Ddatabase triggers also don’t let you perform table-level constraints, because they’re run after a DML statement begins a transaction against a table.”

Chapter 6

Page 149:

The following sentences should be added to the bottom of the second paragraph. They clarify the difference between creating nested tables and VARRAYs, or Table View Columns (TVCs) as a column data type in a table definition.

There’s an internally managed link that connects the EMPLOYEE table with the nested HOME_ADDRESS table, and another link that connect the STREET_ADDRESS table to the HOME_ADDRESS column (or nested table). Collections of varrays don’t require a nested table syntax because their maximum size is known at compile time. Also, varray columns typically store their values in-line.

If you replace the STREET_LIST data type with a STREET_ARRAY data type in the ADDRESS_TYPE, you would re-write the DDL statement that creates the EMPLOYEE table, as follows:

SQL> CREATE TABLE employee
  2  ( employee_id    NUMBER
  3  , first_name     VARCHAR2(20)
  4  , middle_name    VARCHAR2(20)
  5  , last_name      VARCHAR2(20)
  6  , home_address   ADDRESS_LIST)
  7  NESTED TABLE home_address STORE AS address_table;

Page 151:

Missing the “IN” from the in-line CHECK (gender IN (‘M’, ‘F’)) on line 5. The corrected code block should look like this:

SQL> CREATE TABLE club_member
  2  ( club_member_id  NUMBER PRIMARY KEY
  3  , first_name      VARCHAR2(30)
  4  , last_name       VARCHAR2(30)
  5  , gender          CHAR(1) DEFAULT 'F' CHECK (gender IN ('M', 'F')));

Page 161:

The prototype is missing the “COMMENT=’Some comment’“, and “CHARSET=’Some character set’” phrases from the basic table prototype:

, [...]) AUTO_INCREMENT=start_with_value
         ENGINE=engine_list
         CHARSET=utf8
         COMMENT='Some comment.';

Page 163:

There is a missing sentence that qualifies that foreign key columns must have unsigned integer or double data type when they reference a column that uses an unsigned numeric data type (see post as sidebar).

Surrogate primary keys should start with 1 and have the potential to grow as large as necessary, which means their columns should always be unassigned integers or doubles. Foreign keys must also use the same unsigned data type or they’ll raise an ERROR 1005 error. The rule of thumb creates them as integers until they approach the maximum value, and then you should change them to a double data type.

Page 165:

The word “maximum” should be “minimum” in the second sentence of the third paragraph, like:

You provide the integer type and an UNSIGNED keyword when you want zero to be the maximum minimum” positive number.

Page 178:

The Index section was constrained by the length of the book. The following expands on the Oracle Indexes section:

CREATE INDEX common_lookup_nuidx
  ON common_lookup (common_lookup_table);

CREATE INDEX common_lookup_nuidx
  ON common_lookup (common_lookup_table, common_lookup_column);

CREATE UNIQUE INDEX common_lookup_uidx
  ON common_lookup ( common_lookup_table
                   , common_lookup_column
                   , common_lookup_type);

CREATE INDEX common_lookup_nuidx
  ON common_lookup ( common_lookup_table, common_lookup_column, common_lookup_type);

CREATE CLUSTER sales_records ( cost_center_id NUMBER(4)) SIZE 512;

CREATE INDEX sales_uidx ON CLUSTER sales_records;

CREATE TABLE eastern_region ( <column_list> );
CLUSTER sales_records (cost_center_id);
CREATE TABLE western_region ( <column_list> );
CLUSTER sales_records (cost_center_id);

CREATE INDEX sales_uidx
ON employee ( salary + commission_percent, salary, commission_percent );

SELECT   employee_id
,        first_name
,        last_name
,       (salary + commission_percent) AS annual_salary
FROM     employee e
WHERE   (salary + commission_percent) > 100000
ORDER BY annual_salary DESC;

Page 178:

The following introduction and example should include how to use COMMENT and CHARSETphrases, like:

You would set auto incrementing to start at 1001 with a COMMENT phrase that explains why the first thousand rows are skipped with this type of syntax:

CREATE TABLE TABLE_NAME (...) AUTO_INCREMENT=1001 ENGINE=MEMORY
COMMENT='Leave a 1000 rows for seeding values.';

Chapter 7

Page 185:

The first ALTER USER command is missing the BY key word, it’s replaced in the following:

ALTER USER stanley IDENTIFIED BY stanley;

Page 195:

Line #8 needs to be added to the sample query because without it two tables with the same constraint name may be cross joined in the result set.

1
2
3
4
5
6
7
8
9
10
11
12

SELECT   tc.constraint_name
,        tc.constraint_type
,        kcu.ordinal_position
,        kcu.column_name
FROM     table_constraints tc JOIN key_column_usage kcu
ON       tc.table_name = kcu.table_name
AND      tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.table_name LIKE CONCAT('%',@sv_table_name,'%')
ORDER BY tc.table_name
,        tc.constraint_type
,        kcu.ordinal_position;

Page 210:

The following additional text and examples for dropping table-level constraints were omitted by error, and should be added at the top of page 210.

You can remove a NOT NULL constraint by modifying the column (shown in an earlier example):

mysql> ALTER TABLE calendar
    ->   MODIFY end_date DATE NULL;

You can remove a FOREIGN KEY constraint with the following syntax:

mysql> ALTER TABLE calendar
    ->   DROP FOREIGN KEY fk_calendar_1;

Page 213:

The instruction and example for dropping an INDEX is missing at the end of the Oracle Index Maintenance section. The following sentence and code example should be added:

You can remove the index with the DROP INDEX statement, like so:

SQL> DROP INDEX nk_rental_item;

Chapter 8

Page 218:

Clarification about the difference between the Oracle and MySQL generic prototype is missing, and only the MySQL prototypes are thereby provided. The next to last sentence should qualify the differences and explain the MySQL engine difference for LOW_PRIORITY, DELAYED, HIGH_PRIORITY, and IGNORE options, and a new sentence should address the proprietary ON_DUPLICATE_KEY clause. Also, the INTO clause should follow not precede the optional keywords in MySQL and an expression or the column DEFAULT value is valid syntax for the VALUES clause parameters.

The generic prototype for an INSERT statement is confusing when it tries to capture both the VALUES clause and the result set from a query. Therefore, I’ve opted to provide two MySQL prototypes and they include the LOW_PRIORITY, DELAYED, HIGH_PRIORITY, and IGNORE keywords, which aren’t available in the ANSI standard or Oracle syntax. These options only work when you define the table with a MyISAM engine (discouraged by Oracle due to problems with the MyISAM engine). Likewise, Oracle does not support the MySQL specific ON_DUPLICATE_KEY option. The first uses the VALUES clause.

1
2
3
4
5
6
7
8
9

INSERT
[{LOW_PRIORITY | DELAYED | HIGH_PRIORITY}] [IGNORE] INTO TABLE_NAME
[(column1, column2, column3, ...)]
VALUES
({value1 | DEFAULT}, {value2 | DEFAULT}, {value3 | DEFAULT}, ...)
[ON DUPLICATE KEY
 UPDATE column_name1 = expression1
 [,     column_name2 = expression2
 [, ... ]]];

Page 219:

The INTO keyword should follow not precede the MySQL-only LOW_PRIORITY, DELAYED, HIGH_PRIORITY, and IGNORE options in both prototypes on this page. Also, an expression or the column DEFAULT value is valid syntax for the right operand in the SET clause.

1
2
3
4
5
6
7
8
9

INSERT
[{LOW_PRIORITY | DELAYED | HIGH_PRIORITY}] [IGNORE] INTO TABLE_NAME
SET column_name1 = {expression1 | DEFAULT}
,   column_name2 = {expression2 | DEFAULT}
[, ...]]
[ON DUPLICATE KEY
 UPDATE column_name1 = expression1
 [,     column_name2 = expression2
 [, ... ]]];

1
2
3
4
5
6
7
8
9

INSERT
[{LOW_PRIORITY | DELAYED | HIGH_PRIORITY}] [IGNORE] INTO TABLE_NAME
( SELECT value1, value2, value3, ...
  FROM   some_table
  WHERE  some_column = some_value)
[ON DUPLICATE KEY
 UPDATE column_name1 = expression1
 [,     column_name2 = expression2
 [, ... ]]];

Page 234:

It seems that the MySQL TIMESTAMP data type should be added in the second full paragraph on this page, as noted below.

The DATETIME and TIMESTAMP data types in MySQL is a are date-time type equivalents to the DATE data type in an Oracle database.

Page 236:

There an error in the first sentence of the note on this page.

Remember that you can use scalar subqueries in MySQL or Oracle but they do not exist in Oracle. Subqueries in the VALUES clause can return only a single row when matched with literal values or scalar subqueries.

Page 237:

While this isn’t an error because the NOTE covers the MySQL casting behavior along with coverage in Chapter 6 (page 166-169), somebody suggested that it would have been nice to repeat the equivalent Oracle casting examples (page 222) for the MySQL section. Adding it like this probably works:

Like the CAST function in the Oracle database, the CAST function requires that you conform to the default format masks in MySQL (yyyy-mm-dd or yyyymmdd), which would work like this:

,  CAST('2011-04-15' AS DATE)

Whereas, this non-conforming syntax would fail:

,  CAST('2011-15-04' AS DATE)

Raising the following error:

ERROR 1292 (22007): Incorrect datetime VALUE: '20001504'

Chapter 9

Page 251:

ORDER BY 2, 3, 4;

The wrong suite number is referenced in the second paragraph on page 251 that precedes the UPDATE statement.

“Let’s assume you want to change the Suite 322 525 in the second row to Suite 521. The UPDATE statement would look like this when you replace the entire structure:”

Page 255:

The query results should display Bears as follows:

+-------------+-----------------+---------------+
| teeshirt_id | teeshirt_slogan | teeshirt_size |
+-------------+-----------------+---------------+
|           3 | Bears           | Medium        |
+-------------+-----------------+---------------+

Chapter 10

Page 268:

The explanation of how the DELETE statement works with a nested table.

“This works only on collections of user-defined object types. It doesn’t work for nested tables built as collections of a scalar data type, such as a date, number, or string. These single column collections are labelled as Attribute Data Types (ADT) in the Oracle Database 11g documentation. You must replace the collection of a scalar data type with a new collection that doesn’t include the undesired element. You can use a PL/SQL function that accepts an ADT as a function parameter and returns an ADT of the same data type to accomplish this. PL/SQL lets you read through and eliminate undesired elements from any nested table structure, which includes an ADT. While reading the records, you can …”

Chapter 11

Page 275:

A reader suggested that a bullet list should follow the SELECT prototype on this page, like the following:

The SELECT-list is determined by columns listed in the SELECT statement, which are determined by the columns available in the set of tables qualified by the FROM clause. The FROM clause isn’t required when you query from the DUAL pseudo table. MySQL doesn’t require the DUAL pseudo table when querying string or numeric literals. All other clauses are optional. The clauses of a SELECT statement are defined below:

Page 277:

The second paragraph requires a qualifier to explain why piped concatenation isn’t fully supported in MySQL, and it should read like:

The two vertical bars (||) are pipes, and when you use them to glue strings together, it’s known as piped concatenation. MySQL doesn’t fully support piped concatenation because function can’t handle call parameters with piped concatenation, so you use a built-in function to glue strings together, like so:

The paragraph below the output of dates and numbers should reverse right-aligned and left-aligned, and it should read like:

The output is right-aligned left-aligned, which means it’s formatted as a number, because strings are displayed as left-aligned right-aligned..

Page 296:

An additional sentence should clarify the outcome of a BETWEEN operator when either of the end point values is null. It should be added at the end of the paragraph that precedes the In-set Comparisons section, and read like:

More or less, the first column’s truth is whether the value is between the others and the second column’s truth whether it isn’t. The BETWEEN operator returns false whenever one or both of the range values is null.

Page 297:

It was suggested that a comment on negation operators should be in the next to last paragraph of this page. The lease intrusive way to include these would be something like the following:

Although the sample evaluates only a single logical condition, each WHEN clause supports any number of AND or OR logical operators; and their negation equivalent AND NOT or OR NOT logical operators. Any comparison phrase can use the standard equality and inequality comparison operators; the IN, =ANY, =SOME, and =ALL lookup operators; the negation NOT IN, NOT =ANY, NOT =SOME, and NOT =ALL lookup operators; and scalar, single-row, multiple-row, and correlated subqueries.

Page 300:

The LEAST function appears twice in Table 11-1, and should be removed on page 300.

Page 301:

The description column of LEAST function on this page in Table 11-1 should change as follows:

Page 305:

The MONTH function was omitted from in Table 11-1, and should be included on page 305.

The SYSDATE function should have open and close parentheses in the code example on page 305.

Page 306:

Line 2 of the query at the bottom of the page misses a comma:

SQL> SELECT   t.transaction_account AS "Transaction"
  2  ,          LPAD(TO_CHAR
  3              (SUM
  4                (CASE
  5                   WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
  6                        EXTRACT(YEAR FROM transaction_date) = 2011 THEN
  7                     CASE
  8                       WHEN t.transaction_type = 'DEBIT' THEN
  9                         t.transaction_amount
 10                       ELSE
 11                         t.transaction_amount * -1
 12                     END
 13                 END),'99,999.00'),10,' ') AS "JAN"
 14  FROM     TRANSACTION t
 15  GROUP BY t.transaction_account;

Page 307:

Line 2 of the query in the middle of the page misses a comma. This syntax only works in this form when the IGNORE_SPACE option is enabled as part of the SQL_MODE of the MySQL Monitor. This blog post discusses the problem with the syntax when the IGNORE_SPACE option is disabled.

mysql> SELECT   t.transaction_account AS "Transaction"
  2 -> ,          LPAD(FORMAT
  3 ->              (SUM
  4 ->                 (CASE
  5 ->                  WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
  6 ->                       EXTRACT(YEAR FROM transaction_date) = 2011 THEN
  7 ->                    CASE
  8 ->                      WHEN t.transaction_type = 'DEBIT' THEN
  9 ->                        t.transaction_amount
 10 ->                      ELSE
 11 ->                        t.transaction_amount * -1
 12 ->                    END
 13 ->                END),2),10,' ') AS "JAN"
 14 -> FROM     TRANSACTION t
 15 -> GROUP BY t.transaction_account;

Page 318:

A NOTE should have been added at the end of the topmost paragraph on page 318 that says:

NOTE: MySQL doesn’t support a FULL [OUTER] JOIN syntax, and you must use a UNION set operator that splices together a LEFT and RIGHT JOIN.

Page 318:

The NOT NULL on lines 6 and 7 should be replace with a NULL, as shown below:

SQL> SELECT   m.account_number
  2  ,        c.last_name || ', ' || c.first_name AS customer_name
  3  FROM     member m FULL JOIN contact c;
  4  ON       m.member_id = c.member_id
  5  WHERE    m.member_id IS NULL
  6  AND      c.member_id IS NULL;

Page 320:

The NOT NULL on lines 5 and 11 should be replace with a NULL, as shown below:

SQL> SELECT   m.account_number
  2  ,        c.last_name || ', ' || c.first_name AS customer_name
  3  FROM     member m LEFT JOIN contact c;
  4  ON       m.member_id = c.member_id
  5  WHERE    m.member_id IS NULL
  6  UNION ALL
  7  SELECT   m.account_number
  8  ,        c.last_name || ', ' || c.first_name AS customer_name
  9  FROM     member m RIGHT JOIN contact c
 10  ON       m.member_id = c.member_id
 11  WHERE    m.member_id IS NULL;

Chapter 12

Page 343:

The square brackets denoting “OPTIONALLY” as optional in the prototype definition should be removed because the “OPTIONALLY” clause is required to precede the “ENCLOSED BY ‘”‘” clause.

The “ESCAPED BY ‘\\’” clause doesn’t belong in the “SELECT INTO OUTFILE” prototype.

The “OPTIONALLY” keyword must precede the “ENCLOSED BY ‘”‘” clause, and the “ESCAPED BY ‘\\'” clause should be removed because it doesn’t belong.

mysql> SELECT * INTO OUTFILE 'c:/Data/mysql/employee.txt'
  2 -> FIELDS TERMINATED BY ','
  3 -> OPTIONALLY ENCLOSED BY '"'
  4 -> LINES TERMINATED BY '\r\n'
  5 -> FROM employee;

Chapter 13

Page 362:

The word “envirnonment” is misspelled in the fourth sentence of the second paragraph.

Standard out is the output stream of a programming envirnonment environment and it typically prints text to the console (monitor).

Page 368:

The third columns title in Table 13-2 should be “AQL SQL Call Parameter.”

Page 376:

It was suggested that the PIPE ROW function call should be in the text following the example. The following adds it to the end of the second sentence in the second paragraph on the page.

… lines 13 through 18), and line 24 converts the row through a pipe from a PL/SQL data type to a SQL data type with a call to the PIPE ROW function. The ROW function takes a single call parameter, which must be a reference to a scalar variable or a PL/SQL record type stored inside a SQL or PL/SQL varray or table collection.

Page 380:

The Fibonacci algorithm is wrong because it doesn’t return 0 or two 1 values, and no function should have two return values (Fibonacci sequence is: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, … and so on. The return values should be replaced with assignments to a single variable and a single return statement at the bottom. The corrected program returns n rather than a 0 on line 6 in the base case, as shown:

SQL> CREATE OR REPLACE FUNCTION fibonacci
  2  ( n BINARY_DOUBLE ) RETURN BINARY_DOUBLE IS
  3    r BINARY_DOUBLE;
  4  BEGIN
  5    IF n < 2 THEN
  6      r := n;
  7    ELSE
  8      r := fibonacci(n - 2) + fibonacci(n - 1);
  9    END IF;
 10    RETURN r;
 11  END fibonacci;
 12  /

Page 381:

The third sentence in the third paragraph of the Procedure Architecture section fails to setup a comparative and should be replaced by the following:

A pass-by-value model accepts values to perform a task, while a pass-by-value pass-by-reference model accepts values or references to perform a task.

Page 395:

The definition of “NUMBER(6-1)” on line 2 of the first program should be “NUMBER(7,2).”

SQL> DECLARE
  2    lv_number1  NUMBER(7,2);
  3    lv_number2  NUMBER(15,2) := 21533.22;
  4  BEGIN
  5    lv_number1 := lv_number2;
  6    dbms_output.put_line(lv_number1);
  7  END;
  8  /

Page 399:

The beginning regular expression ‘^.+’ won’t capture a movie title that begins with Harry and it should use ‘(^|^.+)’ as a subexpression. A subexpression occurs when you use parentheses to enclose a series of possible conditions. This one is a choice between two criteria – the start of a string or after one or more characters from the beginning of a string. Below is the full correction to line 14 of the sample program that starts on page 399 and ends on the following page:

14

WHERE   REGEXP_LIKE(item_title,'(^|^.+)'||cv_search||'.+','i')

Page 403:

The Guard Exit Loop drawing should have the exit from the decision diamond not the Iterative Step rectangle. The corrected Figure 13-1 should look like this:

Page 418:

The suggestion was made that a smaller example of input and output variables should appear in the middle of the last paragraph on the page. This is how that could be modified:

“The preceding dynamic statements have used only placeholders as inputs. The next example shows you how to use placeholders as inputs and outputs. This type of statement can only work with an insert, update, or delete statement that uses a RETURNING column_name INTO local variable clause.”

SQL> DECLARE
  2    stmt            VARCHAR2(4000);
  3    lv_item_id      NUMBER := 1008;
  4    lv_item_title1  VARCHAR2(60) := 'Die Another Day';
  5    lv_item_title2  VARCHAR2(60);
  6  BEGIN
  7    stmt := 'UPDATE item '
  8         || 'SET    item_title = :item_title_in '
  9         || 'WHERE  item_id = :item_id_in '
 10         || 'RETURNING item_title INTO :item_title_out';
 11    EXECUTE IMMEDIATE stmt USING IN lv_item_title1, IN lv_item_id RETURNING INTO lv_item_title2;
 12    dbms_output.put_line('['||lv_item_title2||']');
 13  END;
 14  /

“The RETURNING column_name INTO local variable clause can only be used with a SELECT statement when you return the value into a PL/SQL cursor data type. You can enclose the NDS query in an anonymous PL/SQL block and return the value using a SELECT column_name INTO local variable clause and an OUT mode variable. The following shows you that technique.”

SQL> DECLARE
  2    stmt VARCHAR2(4000);
  3    lv_item_id    NUMBER := 1008;
  4    lv_item_title VARCHAR2(60);
  5  BEGIN
  6    stmt := 'BEGIN '
  7         || '  SELECT item_title INTO :item_title '
  8         || '  FROM   item '
  9         || '  WHERE  item_id = :item_id_in;'
 10         || 'END;';
 11    EXECUTE IMMEDIATE stmt USING OUT lv_item_title, IN lv_item_id;
 12    dbms_output.put_line('['||lv_item_title||']');
 13  END;
 14  /

“While semicolons are typically excluded from NDS statements that involve DML statements, they’re required when you enclose the code in an anonymous PL/SQL block. The query requires a semicolon on line 9 and the anonymous block requires one on line 10.”

“The next program demonstrates an input placeholder while returning the result set as a weakly typed reference cursor. Unlike the collection examples … “

Chapter 14

Page 429:

Somebody thought I should rework the last sentence in the third paragraph of this page. They appear correct, and here’s the new sentence:

“You don’t have to use the $$ if you would prefer to use the \g or \G to compile the program, but it seems some published materials exclude these options. Even after you define the DELIMITER from a ; (semicolon) to $$, you can still compile a procedure or function with a \g or \G command because they’re natural alternatives to the semicolon. Some published material doesn’t qualify that a default DELIMITER of a ; (semicolon) is equivalent to a \g or \G, and the \g or \G still work when it comes to compiling a stored program unit because they’re unaltered by resetting the default DELIMITER.”

Page 436:

The order of declaration statements isn’t only a suggestion, it’s a requirement. That’s why I’m appending the warning below.

“Declaration elements must be at the top of any block in MySQL stored programs, and they must also precede anything that would belong in an execution block. The order for the declaration group is variables, CONDITION variables, cursors, and then handlder. If you alter the order of the declaration elements, your stored functions and procedures won’t compile..”

Page 443:

There’s a missing the in the last sentence of the fourth paragraph. The sentence should read:

“As with the unary() function example earlier, this queries the results in MySQL Monitor.”

Page 453:

A comment in the member_contact procedure references “first” when it should reference “second“, which is found on line 66 in the procedure. The corrected comment block should look like this:

66
67

 ->   /* Insert into the second table in sequence based on
 ->      inheritance of primary keys by foreign keys. */

Page 467:

A debugging comment drops “least” from the code example on page 467 to fit within the code length print specification of 62 characters. The corrected comment statement should look like this:

18

 ->     SELECT "They don't match because at least one is null!" AS "One Null";

Page 476:

The prototype should use REPEAT not LOOP at the top of the REPEAT UNTIL loop. The corrected prototype block should look like this:

label: REPEAT
  statement_block;
  IF expression THEN LEAVE loop_label; END IF;
END LOOP;

Page 477:

There’s an unnecessary “, “Hi”” on the second line of the query inside the IF-block. The corrected version is unwrapped to a single line below:

  IF fetched = 0 THEN    
    /* Print to the console for each iterartion in the loop. */
    SELECT CONCAT('Movie Title [',lv_movie_title,']') AS "Movie Title";
  END IF;

Page 481:

A comment in the prepared_dml procedure references “two parameter markers” when it should reference “one parameter marker”. The corrected comment block should look like this:

  /* Set a session variable with one parameter marker. */

Page 484:

Line 49 is missing a closing apostrophe from the second call parameter to the CONCAT function, like ‘ DROP FOREIGN KEY ‘ The corrected lines for the session variable assignment should look like this:

47 ->  /* Set a SQL statement by using concatenation. */
48 ->  SET @SQL := CONCAT('ALTER TABLE ',lv_table_name
49 ->                    ,' DROP FOREIGN KEY ',lv_constraint_name);

The PROCEDURE keyword is missing from the grant statement at the bottom of the page.

66

GRANT EXECUTE ON PROCEDURE lib.dropForeignKeys TO student;

Page 485:

The PROCEDURE keyword is missing from the grant statement at the top of the page.

66

GRANT EXECUTE ON PROCEDURE lib.dropForeignKeys TO 'student'@'localhost';

Chapter 15

Page 495:

There was a suggestion to add the following for clarity to the last sentence of the last paragraph of the Oracle DML Triggers sction:

“As mentioned, the execution statements can’t contain any TCL statements, such as SAVEPOINT, ROLLBACK, or COMMIT, unless you’ve designated the trigger as autonomous.”

Page 502:

The following qualification needs to be the last sentence of the third paragraph, as follows:

“It would be easy if MySQL stored programs supported a raise exception syntax like PL/SQL, but they don’t prior to MySQL 5.5.”

Page 503:

After the ERROR 1264 message, at the top of the page, the following introduction paragraph should be added with the code snippet:

“Beginning with MySQL 5.5, you could substitute the following SIGNAL syntax in the if-block:“

45 -> IF matched = 0 THEN
46 ->   SIGNAL SQLSTATE '22003';
47 -> END IF;

There’s an extra need in the second sentence of the fifth paragraph. The sentence should read:

“Although you only need to implement need one trigger block to write a minimal compound trigger, you can have up to four.”

Page 506:

Somebody suggested that an explanatory NOTE should exist immediately after the introduction of “who-audit” in the second paragraph on this page. Since the When You Want It All sidebar introduces “who-audit” in Chapter 6, maybe a parenthetical remark is better here.

“The aforementioned compound trigger populates created_by and created_by columns as part of the application’s “who-audit” information. (See the “When You Want It All” sidebar’s description in Chapter 6.)“

Appendix

Page 513:

The answer Chapter #1, Question #5 is false but it repeats the answer description for question #4, and should be replaced with the following description.

“False. No, most databases support both IPC and TCP/IP connections, which is the case for Oracle and MySQL databases MySQL can’t support distributed transactions across two engines because only one engine supports distributed transactions through the X/Open XA protocol and that’s the InnoDB engine.”

Page 514:

Question #10 shouldn’t have the ending clause, and should appear as:

“True. Sequential log files are maintained by programs in MySQL databases as they are for Oracle databases, notwithstanding the engine of implementation.”

Index

Page 556:

BINARY option for strings, 171, 295-296

Page 558:

component selectors, 42, 125, 357

Page 560:

DATE Oracle, 142, 222

Page 562:

FOREIGN KEY constraints, 109-110

columns, 150-151, 153-154, 200, 205

KEY references, 165

MySQL, 111-112, 174-175, 205

MySQL, 110-111, 200

Page 565:

LEAVE statement, 473-476, 476-480, 478 482

Page 573:

SHOW CREATE TABLE, 162

SHOW TABLE STATUS LIKE, 234

Page 577:

unsigned integers, 111, 141

Page 578:

who-audit component, 168, 314, 506