Errata

"These subtypes include Natural (all integers greater than zero,..."

Now reads:

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

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"

"NATURAL
0 through 2^31"

Now reads:

"NATURAL
1 through 2^31"

-1"

SELECT isbn title

to

SELECT isbn, title

In the last section of code, the third line did read:

"the first parameter is date."

It now reads:

"the second parameter is date."

datatype to the column of the table EMP now reads:

emp_id emp.empno%TYPE

and not:

emp_id emp_empno%TYPE

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

"order_date >= ADD_MONTHS ...
'O';"

Now reads:

"(order_date < ADD_MONTHS (SYSDATE -2) AND
order_status = 'O'));"

to "IF order_date"

reads:

conjunction with SET TRANSACTION READ ONLY

and not:

conjunction with SET TRANSATION READ ONLY

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

cursor" to "not explicitly close a cursor"

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;

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

read:

CLOSE call_cur;

Now reads:

CLOSE caller_cur;

ELSE

to:

ELSEIF :order.count_orders = 1

The last line on the page did read:

EXIT WHEN caller_rec%NOTFOUND;

Now reads:

EXIT WHEN caller_cur%NOTFOUND;

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

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

record_locked EXCEPTION;

did read:

EXIT:
EXIT WHEN condition;

It now reads:

EXIT;
EXIT WHEN condition;

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

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

The second line of the second to last paragraph now reads:

"...not the employee_id)"

It now reads:

"...not the pet_id)"

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.

The 15th line in the code sample now reads:

error_msg VARCHAR2 (300) := SQLERRM;

Should read:

error_msg VARCHAR2 (512) := SQLERRM;

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

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.

"front-end"

Change

/--

to

--

Change

old_company_rec.address1 = new_company_rec.address1 AND

to

old_company_rec.address1 = new_company_rec.address1

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.

Implicit Cursor" did read:

WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3);

Now reads:

WHERE sold_on < ADD_MONTHS (SYSDATE, -3);

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

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.

The 7th line of code from the bottom did read:

primary_keys_tabtype

It now reads:

company_keys_tabtype

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

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

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

changed to "Anchorage"

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

"The date component of date_entered..."

It now reads:

"The date component of request_date..."

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

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

read:

TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994

Now reads:

TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-MAR-1994

"DD-MON-YYYY"

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

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.

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"

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

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

name_format_in IN := 'LAST, FIRST')

should be

name_format_in IN VARCHAR2 := 'LAST, FIRST')

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

has been changed to "pct_stg".

"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)."

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;

deleted and replaced with:

If the department does not exist, I return NULL.

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

option installed)" to "No; normalized coumns contain scalar values
only"

and row 3, column 4: changed "Yes" to "Yes (requires objects option
installed)"

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;

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

"FOO_t" to "FOO_T"

FROM user_tables
to
FROM user_object_tables

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

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

the line, as follows:

SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)

Table at bottom did read:

WHERE type_code =

Now reads:

WHERE typecode =

PACKAGE list

and

END list;

have been changed to

list_pkg.

/* 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.)

read:

GROUP BY D.department_id, name;

COPY_IN

to:

COPY and NAME_IN

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

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