MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle & MySQL Handbook

with 3 comments

My new Oracle Database 11g & MySQL 5.6 Developer Handbook will be available at Oracle Open World 2011 (OOW2011). It’s a great book to compare and contrast approaches in Oracle and MySQL. It covers Oracle SQL & PL/SQL and MySQL SQL & SQL/PSM (Persistent Stored Modules – functions and procedures). Unfortunately, it seems like the book won’t be available on amazon.com until much later in the month, and not available from amazon.de until November. You can read about it at McGraw Hill Professional’s web site. They’ve made a special effort to get copies to OOW2011. Here’s the source code for the book because I don’t know when it’ll be on the publisher’s web site.

I’ll also be at OOW2011. They’ve scheduled me in the bookstore (probably 2nd floor of Moscone North, as usual) from 10 to 10:30 A.M. on Monday and Wednesday for a book signing. If you’re at OOW2011 and you like to stop by and say hello, I look forward to meeting you. Many folks leave comments on the posts but only a few suggest what they’d like me to write on when I’ve got a chance, and you can do that if you stop by to chat.

It’s also interesting to know how many folks use both Oracle and MySQL (any updates on that are always appreciated). Last year at the Bioinformatics and Computation Biology (ACM-BCB 2010) Conference in Niagara Falls, I found it interesting to discover how many pharmaceutical companies and national labs were using both Oracle and MySQL. They appeared consistent about using Oracle for their systems governed by legal compliance rules and MySQL for actual research.

The pharmaceutical companies also had clear barriers between the researchers and professional IT staff, specifically the DBAs. It seems that the DBAs don’t want to cede any control over installed Oracle instances, and they place barriers to research by denying additional Oracle instances when their site licenses would allow them to do so at no incremental cost. On the other hand, the DBAs are fine with letting researchers host and pilot with the MySQL Community Edition databases. This book supports those trying to figure out how to write portable SQL and how to port solutions from MySQL to Oracle and vice versa.

Hope to meet a few new folks at OOW2011. The Kindle version of the book became available 11/25/2011.

As an addendum to this original post, some folks asked for the summary of content for the new book, and the location of the errata (the errors of omission and commission in the book). Below is a summary of the book from page XVIII of the Introduction, and the errata is in the second comment to this post:

Part I: Development Components

  • Chapter 1, “Architectures,” explains the Oracle 11g and MySQL 5.6 development architectures and highlights the comparative aspects of both client and server environments.
  • Chapter 2, “Client Interfaces,” explains and demonstrates the basics of how you use SQL*Plus and MySQL Monitor client software.
  • Chapter 3, “Security,” explains the security barriers for database servers and Data Control Language (DCL) commands that let you manage user and account privileges in the database servers.
  • Chapter 4, “Transactions,” explains the nature of ACID-compliant transactions and the Two-phase Commit (2PC) process demonstrated by INSERT, UPDATE, and DELETE statements.
  • Chapter 5, “Constraints,” explains the five primary database-level constraints and covers the check, not null, unique, primary key, and foreign key constraints.

Part II: SQL Development

  • Chapter 6, “Creating Users and Structures,” explains how you can create users, databases, tables, sequences, and indexes.
  • Chapter 7, “Modifying Users and Structures,” explains how you modify users, databases, tables, sequences, and indexes.
  • Chapter 8, “Inserting Data,” explains how you insert data into tables.
  • Chapter 9, “Updating Data,” explains how you update data in tables.
  • Chapter 10, “Deleting Data,” explains how you delete data from tables.
  • Chapter 11, “Querying Data,” explains how you query data from a single table, from a join of two or more tables, and from a join of two or more queries through set operators.
  • Chapter 12, “Merging Data,” explains how you import denormalized data from external tables or source files and insert or update records in normalized tables.

Part III: Stored Program Development

  • Chapter 13, “PL/SQL Basics,” explains the basics of using PL/SQL to write transactional blocks of code.
  • Chapter 14, “SQL/PSM Basics,” explains the basics of using SQL/PSM to write transactional blocks of code.
  • Chapter 15, “Triggers,” explains how to write database triggers in Oracle and MySQL databases.

Part IV: Appendix

  • Appendix, Covers the answers to the mastery questions at the end of the chapters.

Written by maclochlainn

September 14th, 2011 at 11:44 pm

3 Responses to 'Oracle & MySQL Handbook'

Subscribe to comments with RSS or TrackBack to 'Oracle & MySQL Handbook'.

  1. I’ve been advised that amazon.com received their copies today and the book will start shipping next week. :-) That’s great, but they’ve not sent me my author copies yet. :-( If you find any errors, comment here and I’ll add them to the errata for everyone to use.

    maclochlainn

    1 Oct 11 at 10:46 pm

  2. 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 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 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.”


    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 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 174:

    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.


    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 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 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:

    The query beginning on page 250 and ending on 251 is missing one sort element that’s required to generate the sorted output in the middle of page 251. Line 11 should be changed to add column 4 as the last ORDER BY element:

    11
    
    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 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.

    LEAST
    Finds the most forward date in a set of dates. It works like this to find yesterday:
    SELECT LEAST(SYSDATE -1, SYSDATE)
    FROM dual;

    Page 301:

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

    LEAST
    Finds the most forward earliest date in a set of dates. It works like this to find yesterday:
    SELECT LEAST(SYSDATE - 1, SYSDATE)
    FROM dual;

    Page 305:

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

    MONTH
    Finds the integer value for a month (values 1 to 12), as follows:
    SELECT MONTH(UTC_DATE());

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

    SYSDATE
    Finds the current system date:
    SELECT SYSDATE() FROM dual;

    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:

    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.


    Chapter 12


    Page 336:

    A NOTE could have been added at the end of the paragraph following the code example on page 336, like:

    NOTE: The ON clause can raise an ORA-30926 , unable to get a stable set of rows in the source tables, error when you attempt to resolve using date-time data types. You should convert date-time data types to date data types in the query returned by the USING clause and the target table of the MERGE statement (with the built-in TRUNCate function).


    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 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);

    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

    maclochlainn

    25 Oct 11 at 3:41 pm

  3. Page 300/301 – The Date function “LEAST” shows up twice, once at the bottom of 300, and then another at the top at 301. It also says “Finds the most FORWARD date in a set of dates” (capitalization added) shouldn’t it say “Finds the farthest back date” or something to that effect?

    Jason Ransom

    15 Nov 11 at 7:46 pm

Leave a Reply