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 13
The last sentence in the fifth paragraph did read:

"These subtypes include Natural (all integers greater than zero,..." Now reads: "These subtypes include Natural (all integers greater than or equal to zero,..."

Anonymous    Dec 01, 2000
Printed
Page 56-57
the code that begins on the bottom of page 56 and continues

to the top of age 57 should be replaced with the following code: IF to_number(the_value) > 22 THEN IF max_totals = 0 THEN calc_totals; ELSE WHILE more_data LOOP analyze_results; END LOOP; END IF; END IF;

Anonymous    Mar 01, 1999
Printed
Page 58

The first line of the second code listing on the page did read: "IF to_number(the_value) > 22 AND" Now reads: "IF TO_NUMBER(the_value) > 22 AND"

Anonymous    Jul 01, 2000
Printed
Page 67
The last example in the table, in the column "Same Line for THEN"

contraddicts the point being made. The second ELSIF statement now has the THEN on the same line.

Anonymous    Jul 01, 1999
Printed
Page 67
The first line under the table did read:

"prefer the new line format,..." Now reads: "I prefer the new line format,..."

Anonymous    Dec 01, 2000
Printed
Page 70
In the last line on the page, change "WHEN OTHERS" to "WHEN NO_DATA_FOUND."

Anonymous    Dec 01, 2000
Printed
Page 89
The entry

"NATURAL 0 through 2^31" Now reads: "NATURAL 1 through 2^31"

Anonymous    Jul 01, 2000
Printed
Page 90
bullet item 7, line 2: changed "scale of +1" to "scale of

-1"

Anonymous    May 01, 1998
Printed
Page 90
In the paragraph beginning with "The rounded_million..."

the second sentence now reads "Just as a scale of -1 (instead of +1) ...."

Anonymous    Mar 01, 1999
Printed
Page 94
bullet item 1, "Database to variable conversion", sentence 2

"When you SELECT or FETCH data from a CHAR database column into a VARCHAR2 variable, the trailing spaces are trimmed automatically." changed to "When you SELECT or FETCH data from a CHAR database column into a VARCHAR2 variable, the trailing spaces are retained."

Anonymous    Nov 01, 1998
Printed
Page 106
second code sample, line 2: changed

SELECT isbn title to SELECT isbn, title

Anonymous    May 01, 1998
Printed
Page 109

In the last section of code, the third line did read: "the first parameter is date." It now reads: "the second parameter is date."

Anonymous    Mar 01, 2000
Printed
Page 117
The figure at the top of the page. The example for anchoring a

datatype to the column of the table EMP now reads: emp_id emp.empno%TYPE and not: emp_id emp_empno%TYPE

Anonymous    Jul 01, 1999
Printed
Page 122, 123
In the table describing Subtypes. The 4th from the top in

the left hand column now reads: table_name.column_name%TYPE and not: table_name column_name%TYPE also, in the 5th row of the same table, the left hand column now reads: table_name%ROWTYPE and not: table_name %ROWTYPE and in the 6th row: cursor_name%ROWTYPE and not: cursor_name %ROWTYPE

Anonymous    Jul 01, 1999
Printed
Page 127

At the bottom of the page, in the very last line it did read: "Use a rectype prefix..." but the example shows rectype being used as a suffix. Now reads: "Use a rectype suffix..."

Anonymous    Mar 01, 2000
Printed
Page 129
The first line in the quote in the "Avoid Recycling Variables" section

did read: "...I use in your..." Now reads: "...I use in my..."

Anonymous    Dec 01, 2000
Printed
Page 137
code segment, end of line 2: changed *? to */

Anonymous    May 01, 1998
Printed
Page 137
The code line

"order_date >= ADD_MONTHS ... 'O';" Now reads: "(order_date < ADD_MONTHS (SYSDATE -2) AND order_status = 'O'));"

Anonymous    Jul 01, 2000
Printed
Page 144
In the third line up from the bottom of the page:

"evalutes" should read: "evaluates"

Anonymous   
Printed
Page 144
2nd code example, line 7: changed "OR" to "AND"

Anonymous    Oct 01, 1997
Printed
Page 144
code sample 2 line, 1: changed "IF (order_date"

to "IF order_date"

Anonymous    Aug 01, 1998
Printed
Page 146
The paragraph beginning

"The PL/SQL run-time engine evalutes ..." should read: "The PL/SQL run-time engine evaluates ..."

Anonymous   
Printed
Page 158
At the end of the first paragraph under "The SET TRANSACTION Statement,"

changed "...four flavors:" to "...four flavors. Flavor 1:".

Anonymous    Dec 01, 2000
Printed
Page 159
The last line of the section "The SET TRANSACTION" Statement now

reads: conjunction with SET TRANSACTION READ ONLY and not: conjunction with SET TRANSATION READ ONLY

Anonymous    Jul 01, 1999
Printed
Page 159
At the end of the first paragraph on the page, changed "...consistent:"

to "...consistent. Flavor 2:".

Anonymous    Dec 01, 2000
Printed
Page 159
At the end of the second paragraph, changed "...read-write:" to

"...read-write. Flavor 3:".

Anonymous    Dec 01, 2000
Printed
Page 159
At the end of the fourth paragraph, changed "...released:" to

"...released. Flavor 4:".

Anonymous    Dec 01, 2000
Printed
Page 160
The second paragraph, there was an extra "see"

"Now that you see know the..." The "see" has been deleted.

Anonymous    Jul 01, 1999
Printed
Page 163
Figure 6-1:

The "arrows" should point to the right direction since the cursor is fetched into Program Variable(s), not the Program Variables are fetched to the cursor (errors point to the left).

Anonymous   
Printed
Page 163
line 7 from top of page: changed "not explicitly open a

cursor" to "not explicitly close a cursor"

Anonymous    May 01, 1998
Printed
Page 166
The second paragraph now ends

"...as much control as I can possibly get." and not: "...as much control as I can possible get."

Anonymous    Jul 01, 1999
Printed
Page 167

The 5th and 6th lines of code did read: FROM employees emp, employee_type emptyp WHERE emp.type_code = emptyp.type_code; Now read: FROM employee_type WHERE type_code = :emp.type_code

Anonymous    Dec 01, 2000
Printed
Page 175

In the last code sample, the 3rd line did read: FETCH green_eggs_cur INTO amount_of_ham, num_rejections, reason; Now reads: FETCH green_eggs_cur INTO ham_amount, refused_count, lousy_excuse;

Anonymous    Dec 01, 2000
Printed
Page 177

The 5th line of the second code sample now reads: TO_CHAR (5000-company_rec.total_sales, '$9999')); Should read: TO_CHAR (5000-comp_rec.total_sales, '$9999'));

Anonymous    Dec 01, 2000
Printed
Page 178
Changed the last two sentences in the second paragraph to

The memory for that cursor is released and the number of cursors marked as currently open in your session is decreased by one, pulling you away from the brink of error ORA-01000.

Anonymous    Jul 01, 1999
Printed
Page 179

A little more than half way down the page the code reads: IF NOT caller_cur%ISOPEN THEN OPEN call_cur; END IF; The third line of code should read: OPEN caller_cur

Anonymous    Mar 01, 1999
Printed
Page 180
The last sentence in the third paragraph in the section "The %FOUND

Attribute" did read: "...%NOTFOUND attribute is set to TRUE..." Now reads: "...%FOUND attribute is set to FALSE..."

Anonymous    Dec 01, 2000
Printed
Page 180
The last line in the 1st code sample under "The %FOUND Attribute" did

read: CLOSE call_cur; Now reads: CLOSE caller_cur;

Anonymous    Dec 01, 2000
Printed
Page 181
In the code sample at the top of the page, changed:

ELSE to: ELSEIF :order.count_orders = 1

Anonymous    Dec 01, 2000
Printed
Page 181

The last line on the page did read: EXIT WHEN caller_rec%NOTFOUND; Now reads: EXIT WHEN caller_cur%NOTFOUND;

Anonymous    Dec 01, 2000
Printed
Page 182

The 5th line in the first code sample did read: WHERE TO_NUMBER (TO_CHAR (order_date)) = 1993 Now reads: WHERE TO_NUMBER (TO_CHAR (order_date, 'YYYY')) = 1993

Anonymous    Dec 01, 2000
Printed
Page 187
The first line in the second paragraph did read:

"The IN and IN OUT..." Now reads: "The OUT and IN OUT..."

Anonymous    Jul 01, 2000
Printed
Page 188
Add to end of second to last paragraph on the page

In addition, if your query has more than one table in the FROM clause, then the OF clause will restrict locking to only those tables whose columns are referenced in the OF clause.

Anonymous   
Printed
Page 211

The 16th line in the code sample was missing a semicolon. It now reads: record_locked EXCEPTION;

Anonymous    Dec 01, 2000
Printed
Page 217
In the "Terminating a simple loop" section, the first example of code

did read: EXIT: EXIT WHEN condition; It now reads: EXIT; EXIT WHEN condition;

Anonymous    Mar 01, 2000
Printed
Page 220
bullet item 4, second paragraph: changed "If the first

number is less than the second number" to "If the first number is greater than the second number"

Anonymous    May 01, 1998
Printed
Page 227
para. 2, line 2: changed "folloxwing" to "following"

Anonymous    Aug 01, 1998
Printed
Page 231
code sample 2, line 2: changed

WHILE date_number => 1995 to FOR date_number IN 1994 .. 1999

Anonymous    Aug 01, 1998
Printed
Page 238

The second line of the second to last paragraph now reads: "...not the employee_id)" It now reads: "...not the pet_id)"

Anonymous    Mar 01, 2000
Printed
Page 243

The fourth bullet, the last part of the last sentence now reads: and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.

Anonymous    Jul 01, 1999
Printed
Page 260
Deleted the sentence

"Because there is not one, the exception is unhandled." in the section titled: Exceptions Raised in a Declaration

Anonymous    Jul 01, 1999
Printed
Page 265

The 15th line in the code sample now reads: error_msg VARCHAR2 (300) := SQLERRM; Should read: error_msg VARCHAR2 (512) := SQLERRM;

Anonymous   
Printed
Page 265
code sample, line 16: removed this line

error_info VARCHAR2 (30); -- Info extracted from error_msg.

Anonymous    May 01, 1998
Printed
Page 268
paragraph 5, line 2: changed

Execution of the current PL/SQL block halts immediately, and all effects of the program are rolled back. to Execution of the current PL/SQL block halts immediately, and any changes made to OUT or IN OUT arguments (if present) will be reversed. Changes made to global data structures, such as packaged variables, and to database objects (by executing an INSERT, UPDATE, or DELETE) will <italic>not</italic> be rolled back. You must execute an explicit ROLLBACK in your exception section to reverse the effect of DML operations.

Anonymous    May 01, 1998
Printed
Page 268
line -2 (now top of page 269): changed "frontend" to

"front-end"

Anonymous    May 01, 1998
Printed
Page 279

The first sentence in the first full paragraph did read: "The record or tuple structure..." Now reads: "The table.column structure..." The fourth line in this paragraph did read: "The rest of this chapter show..." Now reads: "The rest of this chapter shows..."

Anonymous    Mar 01, 2000
Printed
Page 282
In the middle of the page

Change /-- to -- Change old_company_rec.address1 = new_company_rec.address1 AND to old_company_rec.address1 = new_company_rec.address1

Anonymous   
Printed
Page 291

The last line of the code in the middle of the page did read: WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3) It now reads: WHERE sold_on < ADD_MONTHS (SYSDATE, -3) ie, BETWEEN has been removed.

Anonymous    Mar 01, 2000
Printed
Page 291
The 13th line of the 1st code example under "SELECT INTO from an

Implicit Cursor" did read: WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3); Now reads: WHERE sold_on < ADD_MONTHS (SYSDATE, -3);

Anonymous    Dec 01, 2000
Printed
Page 292

The second code line on the page did read: WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3); Now reads: WHERE sold_on < ADD_MONTHS (SYSDATE, -3);

Anonymous    Dec 01, 2000
Printed
Page 295

The section "Setting Records to NULL" has been changed to read: Setting records to NULL In earlier versions of Oracle (7.2 and below), the following assignment would cause an error: comp_sales_rec := NULL; NULL was treated as a scalar value, and would not be applied to each of the record's fields. In Oracle 7.3 and above, the assignment of NULL to a record is allowed, and will set each of the fields back to the default value of NULL.

Anonymous    Jul 01, 1999
Printed
Page 302

The last line did read: "5 TABLE OF VARCHAR2(30) INDEXED BY BINARY_INTEGER;" It now reads: "5 TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;"

Anonymous    Mar 01, 2000
Printed
Page 303

The first paragraph, second sentence did read: "The INDEXED BY BINARY_INTEGER clause" It now reads: "The INDEX BY BINARY_INTEGER clause"

Anonymous    Mar 01, 2000
Printed
Page 311
The first line of code at top of the page did read:

"...name in..." Now reads: "...name_in..."

Anonymous    Jul 01, 2000
Printed
Page 311

The second line of code at the top of the page did read: "...company id..." Now reads: "...company_id..."

Anonymous    Jul 01, 2000
Printed
Page 312

The eighth and nineth lines of code currently read: company_ids := company_pkg.id (company_pkg.company_names_tab(v_row)); but should read: company_ids (NVL (company_ids.LAST, 0) + 1) := company_pkg.id(company_pkg.company_name_tab(v_row));

Anonymous    Mar 01, 1999
Printed
Page 323

The 7th line of code from the bottom did read: primary_keys_tabtype It now reads: company_keys_tabtype

Anonymous    Mar 01, 2000
Printed
Page 356
end of para. 2 (para. under "The INSTR function") added

this sentence: "In Oracle7, if nth_appearance is not positive (i.e., if it is 0 or negative), then INSTR always returns 1. In Oracle8, a value of 0 or a negative number for nth_appearance causes INSTR to raise the VALUE_ERROR exception."

Anonymous    Aug 01, 1998
Printed
Page 378

The two lines of code before the last paragraph did read: last_space_loc := INSTR (fullname_int, ' ', -1, 1); lname_out := SUBSTR (fullname_int, delim_space+1); Now read: last_space_loc := INSTR (fullname_int, ' ', -1, 1); lname_out := SUBSTR (fullname_int, last_space_loc+1);

Anonymous    Mar 01, 2000
Printed
Page 381
In the second paragraph, changed

"to the built-in DBMS_OUTPUT package" to: "to the built-in DBMS_OUTPUT package (see disk version)"

Anonymous    Dec 01, 2000
Printed
Page 391-392
the authors use TRANSLATE to change digits to "N" and

letters to "A". In the second PL/SQL example on page 391 and the only example on 392, the translate string is missing the digit "0". The strings should be '0123456789ABCDEF....' instead of '12345...' and the 'NNAAA....' strings should have an extra 'N'.

Anonymous    Mar 01, 1999
Printed
Page 393
After December 31, 4712 A.D., the following has been added

(in Oracle Server 8.0 and higher, the maximum valid date is December 31, 9999)

Anonymous    Jul 01, 1999
Printed
Page 397-398
the section "The NEW_TIME function": "Bombay" has been

changed to "Anchorage"

Anonymous    Jul 01, 1999
Printed
Page 397

The fourth bullet item under "MONTHS_BETWEEN" did read: MONTHS_BETWEEN ('28-FEB-1994', '15-FEB-1994') ==> 0 Now reads: MONTHS_BETWEEN ('28-FEB-1994', '15-FEB-1994') ==> .419

Anonymous    Mar 01, 2000
Printed
Page 402
The last paragraph did begin with:

"The date component of date_entered..." It now reads: "The date component of request_date..."

Anonymous    Mar 01, 2000
Printed
Page 403
para. 3: changed "Here are some examples of TRUNC for dates:"

to "Here are some examples of TRUNC for dates (all assuming a default date format mask of DD-MON-YYYY):"

Anonymous    Aug 01, 1998
Printed
Page 403
bullet items 1, 2, and 6: changed all 7 occurences of

"DD-MON-YY" to "DD-MON-YYYY"

Anonymous    Aug 01, 1998
Printed
Page 403
The second to last bullet item on the page, the second code line did

read: TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994 Now reads: TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-MAR-1994

Anonymous    Mar 01, 2000
Printed
Page 404
bullet item 1: changed 2 occurences of "DD-MON-YY" to

"DD-MON-YYYY"

Anonymous    Aug 01, 1998
Printed
Page 410
Table 13-1: some of the names are matched to the wrong

descriptions, for example, cos for sin, acos, asin Change 4 lines to read as follows: ACOS Returns the inverse cosine ASIN Returns the inverse sine COS Returns the cosine COSH Returns the hyperbolic cosine

Anonymous    Mar 01, 1999
Printed
Page 414

The line that reads: "This number of months then bexcomes..." Should read: "This number of months then becomes..."

Anonymous   
Printed
Page 415, 418, 419
Hyperbolic functions, these seem to include n,

and h in the definitions. They should refer to n or h (i.e. n = h, in these definitions) Changed (h) to (n)in the code examples on those pages.

Anonymous    Mar 01, 2000
Printed
Page 421
The last code block on the page did begin

SQL> GRANT READ ON DIRECTORY projects TO SCOTT; Now reads: SQL> GRANT READ ON DIRECTORY projects TO ELI;

Anonymous    Mar 01, 2000
Printed
Page 425
"The NVL Function," first paragraph; in the last two lines of the code

listing for FUNCTION NVL shown: The third-to-last line of code did read: "RETURN NUMBER" Now reads: "IN NUMBER" The last line of code did read: "RETURN BOOLEAN" Now reads: "IN BOOLEAN"

Anonymous    Jul 01, 2000
Printed
Page 434
In the 9th entry in the "Description" column (SSSSS), changed "1 through

86399" to "0 through 86399."

Anonymous    Dec 01, 2000
Printed
Page 444

The code in the middle of the page did read: TO_DATE ('07-1-1994', 'FXDD-FXMM-FXYYYY') ==> 07-JUL-1994 It now reads: TO_DATE ('07-1-1994', 'FXDD-FXMM-FXYYYY') ==> 07-JAN-1994

Anonymous    Mar 01, 2000
Printed
Page 472
The last line of code in both the first and second bulleted items did

read: END They now read: END;

Anonymous    Dec 01, 2000
Printed
Page 475
These lines

SQL> start updemp 1100 VICE-PRESIDENT SQL> start updemp 1100 PRESIDENT Should read: SQL> start updemp 1100 VICE-PRESIDENT 300000 SQL> start updemp 1100 PRESIDENT 200000

Anonymous   
Printed
Page 499
code line -6

name_format_in IN := 'LAST, FIRST') should be name_format_in IN VARCHAR2 := 'LAST, FIRST')

Anonymous    Mar 01, 1999
Printed
Page 504
under "Default Values", first sentence

"As you have seen from previous examples, you can provide a default value for both IN and IN OUT parameters." changed to "As you have seen from previous examples, you can provide a default value for IN parameters."

Anonymous    Nov 01, 1998
Printed
Page 507
In the code at the top of the page, all references to "char_format"

has been changed to "pct_stg".

Anonymous    Mar 01, 2000
Printed
Page 509
In the paragraph at top of the page, changed "...the local module..."

to "...the particular module of interest..."

Anonymous    Dec 01, 2000
Printed
Page 523
In Figure 16-2, the fourth-to-last line now reads:

DBMS_OUTPUT.PUT.LINE Should read: DBMS_OUTPUT.PUT_LINE

Anonymous   
Printed
Page 525
In the last sentence of third paragraph, the footnote marker for "Booch

diagram" is way up just under the line above. It should be next to "diagram."

Anonymous   
Printed
Page 525
under para. 1, added this paragraph

"Private elements in a package must be defined before they can be referenced by other elements of the package. If, in other words, a public procedure calls a private function, that function must be defined <italic>above</italic> the public procedure in the package body. You can, alternatively, use a forward declaration if you wish to keep your private programs at the bottom of the package body (see Chapter 15, Procedures and Functions, Forward Declarations)."

Anonymous    Nov 01, 1998
Printed
Page 563

The third sentence of the first full paragraph on the page did read: "...query at 11 a.m;..." Now reads: "...query at 10 a.m;..."

Anonymous    Jul 01, 2000
Printed
Page 564
Last line

ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1993 should read ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1994

Anonymous   
Printed
Page 565
The fourth line from bottom of the first code sample on the page did

read: "...payment..." Now reads: "...payment_date..."

Anonymous    Jul 01, 2000
Printed
Page 566

Combining Scalar and Aggregate Values code section did read: SELECT department_id, SUM (salary) FROM employee ORDER BY department_id; Now reads: SELECT department_id, SUM (salary) FROM employee GROUP BY department_id;

Anonymous    Mar 01, 2000
Printed
Page 566
The sixth line of the first code sample in section "Combining Scalar

and Aggregate Values" did read: WHERE E.department_id Now reads: WHERE E1.department_id

Anonymous    Jul 01, 2000
Printed
Page 567
The first two sentences of the second to last paragraph have been

deleted and replaced with: If the department does not exist, I return NULL.

Anonymous    Mar 01, 2000
Printed
Page 569

The first line of the first complete code sample did read: "...SELECT E.department_id..." Now reads: "...SELECT department_id..."

Anonymous    Jul 01, 2000
Printed
Page 575

The code under the second bullet item has been changed to read: SELECT TO_CHAR (note_date, 'DAY') day, SUM (ps_parse.number_of_atomics (text, 'urgent')) urgent_count FROM notes WHERE urgent_count > 0 GROUP BY TO_CHAR (note_date, 'DAY');

Anonymous    Mar 01, 2000
Printed
Page 587
Table 18-1, row 3, column 2: changed "Yes (requires objects

option installed)" to "No; normalized coumns contain scalar values only" and row 3, column 4: changed "Yes" to "Yes (requires objects option installed)"

Anonymous    Aug 01, 1998
Printed
Page 609
Deleted the first appearance of "AS OBJECT" on the page.

The book provides the following syntax for the forward type definition: CREATE TYPE Person_t AS OBJECT; Under Oracle 8.0.5, this syntax results in an error. Use the following command: CREATE TYPE Person_t;

Anonymous    Jul 01, 1999
Printed
Page 614
The second note was deleted.

Anonymous    Mar 01, 1999
Printed
Page 621
para 1, line 3: changed "table alias" to italics

and code sample 2: changed SELECT REF(p) FROM pets p; WHERE ...--uses alias p to SELECT REF(p) FROM pets p -- uses table alias "p" WHERE ... ("REF" is still in bold contant-width font.)

Anonymous    Aug 01, 1998
Printed
Page 641, 642
Table 18-4, column 3: changed all 9 occurences of

"FOO_t" to "FOO_T"

Anonymous    Aug 01, 1998
Printed
Page 641
Table 18-4, row 8, column 3: changed code line

FROM user_tables to FROM user_object_tables

Anonymous    Aug 01, 1998
Printed
Page 667
code sample 2, lines 2 and 3: changed

/* The colors variable begins life initialized but with || no elements allocated */ to /* The colors variable begins life initialized but with || no elements allocated */

Anonymous    Aug 01, 1998
Printed
Page 672-673
moved page break from after

COLUMN_VALUE ------------------- to after COLUMN_VALUE ------------------- RED GREEN

Anonymous    Aug 01, 1998
Printed
Page 674
code sample 1, line 1: added close parenthesis at the end of

the line, as follows: SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)

Anonymous    Aug 01, 1998
Printed
Page 688
The note at the top of the page was deleted.

Anonymous    Mar 01, 1999
Printed
Page 688

Table at bottom did read: WHERE type_code = Now reads: WHERE typecode =

Anonymous    Mar 01, 2000
Printed
Page 749
The first sentence in the first paragraph has been replaced with the

following: When this C function returns, the string referenced by pTimezone will correspond to the current timezone as understood by the Oracle extproc process executing this shared library function.

Anonymous    Mar 01, 2000
Printed
Page 763
In the third example of code, both

PACKAGE list and END list; have been changed to list_pkg.

Anonymous    Mar 01, 2000
Printed
Page 802
In the second to last paragraph, change

"you will be permitted" to: "you are permitted"

Anonymous   
Printed
Page 817

The fifth entry in the Extension column in the table did read: <file>spb Now reads: <file>.spb

Anonymous    Jul 01, 2000
Printed
Page 823
replaced code sample 2 with these lines

/* Filename on companion disk: recomp.sql */ SET PAGESIZE 0 SET LINESIZE 80 COLUMN command_line1 FORMAT A75 COLUMN command_line2 FORMAT A75 SPOOL recomp.cmd SELECT 'ALTER '|| DECODE (object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' '|| object_name || ' ' || DECODE (object_type, 'PACKAGE', 'COMPILE SPECIFICATION;', 'PACKAGE BODY', 'COMPILE BODY;' , 'COMPILE;') command_line1, 'SHOW ERRORS' command_line2 FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') AND status = 'INVALID' ORDER BY DECODE (object_type, 'PACKAGE', 1, 'PACKAGE BODY', 4, 'FUNCTION', 2, 'PROCEDURE', 3) / SPOOL OFF SPOOL recomp.lis START recomp.cmd SPOOL OFF (821, 822 reprinted for page breaks.)

Anonymous    Aug 01, 1998
Printed
Page 828
The OBJ$ heading has been changed to read: OBJ#

Anonymous    Mar 01, 2000
Printed
Page 868
The second and third lines of code from the top have been changed to

read: GROUP BY D.department_id, name;

Anonymous    Mar 01, 2000
Printed
Page 896
In the last paragraph, changed "...following scripts:" to "...following

scripts, compdbg and trace.sql:".

Anonymous    Dec 01, 2000
Printed
Page 902

The second sentence in the fifth paragraph on the page did read: "...earlier in the article..." Now reads: "...earlier in this chapter..."

Anonymous    Dec 01, 2000
Printed
Page 905
Changed

COPY_IN to: COPY and NAME_IN

Anonymous    Jul 01, 1999
Printed
Page 908

The seventh line down in the code sample at top of page did read: IF debuging Now reads: IF tracing

Anonymous    Jul 01, 2000
Printed
Page 925
In the first line of code on the page, "=20" was removed.

Anonymous    Jul 01, 2000
Printed
Page 925
The fourth line of the second code sample on the page was missing ':='.

Now reads: pipe_status := DBMS...

Anonymous    Jul 01, 2000
Printed
Page 926-930
replaced from section "DBMS_AQ (PL/SQL8 Only)" through

section "The STOP_Q procedure" with the following text (I'm including the heading levels in brackets): Oracle AQ, the Advanced Queueing Facility [Head-A] Oracle8 offers the Oracle Advanced Queuing facility (Oracle AQ) which implements deferred execution of work. There are two packages you will use to implement advanced queuing: DBMS_AQ, which contains the queuing procedures themselves, and DBMS_AQADM, which lets you perform administrative tasks. They make extensive use of PL/SQL record structures, as you will see in the individual program interfaces below. For more detail on these records and how to manipulate their contents, see Oracle Built-in Packages. DBMS_AQ (PL/SQL 8 Only) [Head B] The DBMS_AQ package provides an interface to the messaging tasks of Oracle AQ. To use these procedures, you must have been granted the new role, AQ_USER_ROLE. The ENQUEUE procedure [Head C] The ENQUEUE procedure adds a message to an existing message queue. The target message queue must have had enqueuing enabled previously via the DBMS_ AQADM.START_QUEUE procedure. The specification is: PROCEDURE DBMS_AQ.ENQUEUE (q_schema IN VARCHAR2 DEFAULT NULL q_name IN VARCHAR2, corrid IN VARCHAR2 DEFAULT NULL, transactional IN BOOLEAN:= TRUE, priority IN POSITIVE DEFAULT 1, delay IN DATE DEFAULT NULL, expiration IN NATURAL:= 0, relative_msgid IN NUMBER DEFAULT NULL, exception_queue_schema IN VARCHAR2 DEFAULT NULL, exception_queue IN VARCHAR2 DEFAULT NULL, reply_queue_schema IN VARCHAR2 DEFAULT NULL, reply_queue IN VARCHAR2 DEFAULT NULL, user_data IN any_object_type, msgid OUT RAW); The DEQUEUE procedure [Head C] The DEQUEUE procedure can either remove or browse a message from an existing message queue. The target message queue must have had dequeuing enabled previously via the DBMS_AQADM.STOP_QUEUE procedure. The specification is: PROCEDURE DBMS_AQ.DEQUEUE (q_schema IN VARCHAR2 DEFAULT NULL, q_name IN VARCHAR2, msgid IN RAW DEFAULT NULL, corrid IN VARCHAR2 DEFAULT NULL, deq_mode IN CHAR DEFAULT `D', wait_time IN NATURAL DEFAULT NULL, transactional IN BOOLEAN:= true, out_msgid OUT NUMBER, out_corrid OUT VARCHAR2, priority OUT POSITIVE, delay OUT DATE, expiration OUT NATURAL, retry OUT NATURAL, exception_queue_schema OUT VARCHAR2, exception_queue OUT VARCHAR2, reply_queue_schema OUT VARCHAR2, reply_queue OUT VARCHAR2, user_data OUT any_object_type); DBMS_AQADM (PL/SQL 8 Only) [Head B] The DBMS_AQADM package provides an interface to the administrative tasks of Oracle AQ. To use these procedures, a DBMS_AQADM user must have been granted the new role, AQ_ADMINISTRATOR_ROLE. You can verify the results of executing the DBMS_ AQADM package by querying the new Oracle AQ data dictionary views, USER_QUEUE_TABLES and USER_QUEUES (DBA levels of these views are also available). The CREATE_QUEUE_TABLE procedure [Head C] The CREATE_QUEUE_TABLE procedure creates a queue table. A queue table is the named repository for a set of queues and their messages. A queue table may contain numerous queues, each of which may have many messages. But a given queue and its messages may exist in only one queue table. The specification is: PROCEDURE DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table IN VARCHAR2 ,queue_payload_type IN VARCHAR2 ,storage_clause IN VARCHAR2 DEFAULT NULL ,sort_list IN VARCHAR2 DEFAULT NULL ,multiple_consumers IN BOOLEAN DEFAULT FALSE ,message_grouping IN BINARY_INTEGER DEFAULT NONE ,comment IN VARCHAR2 DEFAULT NULL ,auto_commit IN BOOLEAN DEFAULT TRUE); The DROP_QUEUE_TABLE procedure [Head C] The DROP_QUEUE_TABLE procedure drops an existing queue table. An error is returned if the queue table does not exist. The force parameter specifies whether all existing queues in the queue table are stopped and dropped automatically or manually. If manually (i.e., FALSE), then the queue administrator must stop and drop all existing queues within the queue table using the DBMS_AQADM.STOP_QUEUE and DBMS_AQADM.DROP_QUEUE procedures. The specification is: PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table IN VARCHAR2, force IN BOOLEAN default FALSE, auto_commit IN BOOLEAN default TRUE); The CREATE_QUEUE procedure [Head C] The CREATE_QUEUE procedure creates a new message queue within an existing queue table. An error is returned if the queue table does not exist. The required queue_name parameter specifies the name of the new message queue to create. All queue names must be unique within the schema. The specification is: PROCEDURE DBMS_AQADM.CREATE_QUEUE (queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_type IN BINARY_INTEGER default DBMS_AQADM.NORMAL_QUEUE, max_retries IN NUMBER default 0, retry_delay IN NUMBER default 0, retention_time IN NUMBER default 0, dependency_tracking IN BOOLEAN default FALSE, comment IN VARCHAR2 default NULL, auto_commit IN BOOLEAN default TRUE); The ALTER_QUEUE procedure [Head C] The ALTER_QUEUE procedure modifies properties of an existing message queue. It returns an error if the message queue does not exist. Currently, you can alter only the maximum retries, retry delay, retention time, rentention delay and auto-commit properties; Oracle will augment this list in future releases. The specification is: PROCEDURE DBMS_AQADM.ALTER_QUEUE ( queue_name IN VARCHAR2, max_retries IN NUMBER default NULL, retry_delay IN NUMBER default NULL, retention_time IN NUMBER default NULL, auto_commit IN BOOLEAN default TRUE); The DROP_QUEUE procedure [Head C] The DROP_QUEUE procedure drops an existing message queue. It returns an error if the message queue does not exist. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable both enqueuing and dequeuing for the message queue to be dropped. If the message queue has not been stopped, thenDROP_QUEUE returns an error of queue resource busy. The specification is: PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table IN VARCHAR2, force IN BOOLEAN default FALSE, auto_commit IN BOOLEAN default TRUE); The START_QUEUE procedure [Head C] The START_QUEUE procedure enables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to enable both. The specification is: PROCEDURE DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE); The STOP_QUEUE procedure [Head C] The STOP_QUEUE procedure disables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to disable both enqueuing and dequeuing. The wait parameter specifies whether to wait for outstanding transactions or to return immediately. The wait option is highly dependent on outstanding transactions. If outstanding transactions exist, then wait will either hang until the transactions complete or return an error of ORA-24203, depending on whether the wait parameter is set to true or false. The specification is: PROCEDURE DBMS_AQADM.STOP_QUEUE (queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE); (The new text for Appendix C is also on the web at http://www.datacraft.com/opp/dbms_aq.html.) , seq_deviation IN CHAR DEFAULT A

Anonymous    Aug 01, 1998
Printed
Page 928
The third sentence on the page did read:

"...executing the DBMS..." Now reads: "...executing a program in the DBMS..."

Anonymous    Dec 01, 2000
Printed
Page 928
In line 4 of the first paragraph, deleted the extra space between

"DBMS_" and "AQADM."

Anonymous    Dec 01, 2000
Printed
Page 928
In line 5 of the first paragraph, deleted the extra space between

"USER_QUEUE_" and "TABLES."

Anonymous    Dec 01, 2000
Printed
Page 931
In the code sample at bottom of page, the word "in" has been changed

to "IN" in both lines 4 & 6 (to match the rest of the code of the page).

Anonymous    Jul 01, 2000
Printed
Page 947

The last line of first paragraph under "DBMS_SHAPSHOT" did read: "...see Chapter 16 in the Oracle7 Server Administrator Guide." Now reads: "check the lastest version of the Server Administrator Guide in Oracle documentation."

Anonymous    Dec 01, 2000
Printed
Page 952
In the second-to-last code listing on the page, changed:

"...LAST_ERROR_POSTITION..." to: "...LAST_ERROR_POSITION..."

Anonymous    Dec 01, 2000
Printed
Page 956
The fourth line of READ_ONLY section now reads:

"...that transactions start." Should read: "...that transaction's start." {Diskette} There is an error in the code on the diskette that is correct in the book on page 211. The code should read: FUNCTION next_task RETURN task.task_id%TYPE IS /* Cursor of all open tasks, assigned and unassigned */ CURSOR task_cur IS SELECT task_id FROM task WHERE task_status = 'OPEN' ORDER BY task_priority, date_entered DESC; /* The record for the above cursor */ task_rec task_cur%ROWTYPE; /* || An exception for error ORA-00054: || "resource busy and acquire with NOWAIT specified" */ record_locked EXCEPTION; PRAGMA EXCEPTION_INIT (record_locked, -54); /* || Variables which determine whether function should continue || to loop through the cursor's records. */ found_unassigned_task BOOLEAN := FALSE; more_tasks BOOLEAN := TRUE; /* The primary key of the unassigned task to be returned */ return_value task.task_id%TYPE := NULL; BEGIN /* Open the cursor and start up the loop through its records */ OPEN task_cur; WHILE NOT found_unassigned_task AND more_tasks LOOP /* Fetch the next record. If nothing found, we are done */ FETCH task_cur INTO task_rec; more_tasks := task_cur%NOTFOUND; IF more_tasks THEN /* || A record was fetched. Create an anonymous block within || the function so that I can trap the record_locked || exception and still stay inside the cursor loop. */ BEGIN /* Try to get a lock on the current task */ SELECT task_id INTO return_value FROM task WHERE task_id = task_rec.task_id FOR UPDATE OF task_id NOWAIT; /* || If I get to this line then I was able to get a lock || on this particular task. Notice that the SELECT INTO || has therefore already set the function's return value. || Now set the Boolean to stop the loop. */ found_unassigned_task := TRUE; EXCEPTION WHEN record_locked THEN /* Record was already locked, so just keep on going */ NULL; END; END IF; END LOOP; /* || Return the task id. Notice that if an unassigned task was NOT || found, I will simply return NULL. */ CLOSE task_cur; RETURN return_value; EXCEPTION /* || General exception handler for the function: if an error occurred, || then close the cursor and return NULL for the task ID. */ WHEN OTHERS THEN CLOSE task_cur; RETURN NULL; END;

Anonymous   
Printed
Page 961
under the heading UTL_FILE, the author says that "You can,

therefore, interact with operating system files both on the local workstation(client) and on the server disks." This, I believe, is incorrect or at least misleading. Please look at this short thread for further discussion: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=cb7b347afd620c2ae d0d8fa29d5c12dd.29848%40mygate.mailgate.org&rnum=1&prev=/groups%3Fsafe%3Dima ges%26ie%3DISO-8859-1%26as_umsgid%3Dcb7b347afd620c2aed0d8fa29d5c12dd.29848%4 0mygate.mailgate.org%26lr%3D%26hl%3Den {diskette} These lines (from the updemp.sql script on page 475): SQL> start updemp 1100 VICE-PRESIDENT SQL> start updemp 1100 PRESIDENT Should read: SQL> start updemp 1100 VICE-PRESIDENT 300000 SQL> start updemp 1100 PRESIDENT 200000 {diskette} The code for recomp.sql was changed in the book on page 823 but not on the diskette. The code on the diskette should also be the following: SET PAGESIZE 0 SET LINESIZE 80 COLUMN command_line1 FORMAT A75 COLUMN command_line2 FORMAT A75 SPOOL recomp.cmd SELECT 'ALTER '|| DECODE (object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' '|| object_name || ' ' || DECODE (object_type, 'PACKAGE', 'COMPILE SPECIFICATION;', 'PACKAGE BODY', 'COMPILE BODY;' , 'COMPILE;') command_line1, 'SHOW ERRORS' command_line2 FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') AND status = 'INVALID' ORDER BY DECODE (object_type, 'PACKAGE', 1, 'PACKAGE BODY', 4, 'FUNCTION', 2, 'PROCEDURE', 3) / SPOOL OFF SPOOL recomp.lis START recomp.cmd {diskette} db2tab2.sql on companion disk is incorrect in the following way: company_table.company_id (next_row) := company_rec.company_id; should read company_table(next_row) .company_id := company_rec.company_id; etc...

Anonymous   
Printed
Page 966
Deleted the following entry from the index:

Boolean functions returning, 491

Anonymous    Dec 01, 2000
Printed
Page 969, Index
The main listing for Cursor Variables now reads 191, not

169.

Anonymous    Jul 01, 1999
Printed
Page 990
added the following to the end of the next-to-last para.

Whenever possible, our books use RepKover (tm), a durable and flexible lay-flat binding. If the page count exceeds RepKover's limit, perfect binding is used.

Anonymous    Oct 01, 1997