Errata

It says
WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK

However, above does not work.
Here is right use of statement:
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

Note from the Author or Editor:
Make change as described above.

Datetime literals (available since Oracle Database 10g Release 2) should also be mentioned (see http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm). The last paragraph on the page may need to be reformulated accordingly or removed.

Note from the Author or Editor:
REPLACE THIS TEXT...

Even though the database allows you to specify intervals using a literal format, you
cannot do so with DATE datatypes; notice that '01-OCT-1986' is listed as a string rather
than as an Oracle DATE. Yes, PL/SQL or SQL can implicitly convert '01-OCT-1986'
to and from Oracle?s internal date format,? but you will normally use built-in functions
to perform explicit conversions. For example:

TO_DATE('01-OCT-1986', 'DD-MON-YYYY')
TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 −6','DD-MON-YYYY HH24:MI:SS TZH')

Both expressions return October 1, 1986, with zero hours, zero minutes, and zero
seconds; the first in the DATE datatype, and the second in the TIMESTAMP WITH
TIME ZONE datatype. The second expression also includes time zone information;
the −6 represents the number of hours? difference from GMT (UCT).

?As long as the database or session has its NLS_DATE_FORMAT parameter set to DD-MON-YYYY.

...WITH THE FOLLOWING TEXT:

To specify literal values of dates and times, you can use the DATE or TIMESTAMP keyword:

DATE '1986-10-01'
TIMESTAMP '1986-10-01 00:00:00 -6:00'

Both expressions return October 1, 1986, with zero hours, zero minutes, and zero seconds; the first in the DATE datatype, and the second in the TIMESTAMP WITH TIME ZONE datatype. The second expression also includes time zone information; the -6 represents the number of hours? difference from UCT.

Alternatively, you can convert strings to date or timestamp datatypes using Oracle built-in functions. The following are the equivalent of the above literals:

TO_DATE('01-OCT-1986', 'DD-MON-YYYY')
TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH')

In your code, a simple string such as '01-OCT-1986' <i>may</i> get converted to a date datatype; if that happens Oracle will apply the current session's value of the NLS_DATE_FORMAT parameter when attempting the conversion. In this case, only if the format were set to DD-MON-YYYY would you be guaranteed to get the desired result. You probably want to avoid such implicit conversions, since NLS_DATE_FORMAT can change.

declare
pipename varchar2(12) := 'signaler';
result integer:= dbms_pipe.create_pipe(pipename);
begin
dbms_pipe.pack_message('stop');
end;

This requires the send message, as so...
declare
pipename varchar2(12) := 'signaler';
result integer:= dbms_pipe.create_pipe(pipename);
begin
dbms_pipe.pack_message('stop');
result :=dbms_pipe.send_message(pipename);
end;

Note from the Author or Editor:
Change second section of code on page 108 to:

DECLARE
pipename VARCHAR2 (12) := 'signaler';
result INTEGER := DBMS_PIPE.create_pipe (pipename);
BEGIN
DBMS_PIPE.pack_message ('stop');
result := DBMS_PIPE.send_message (pipename);
END;

The code example refers to the exception name "dynsql.invalid identifier".

WHEN dynsql.invalid identifier THEN ...

However in the previous declaration section the variable is named "invalid_identifier" (having an underscore between the two words).

PRAGMA EXCEPTION_INIT (invalid_identifier, -904);

Note from the Author or Editor:
I have made the fix in ch6 for 6th edition. Simply add an underscore:

WHEN dynsql.invalid_identifier THEN ...

In 10g and more it is 4 bytes by CHAR. I could not said for version before. (The book said 3 bytes)

> CREATE TABLE my_strings(vb100 VARCHAR2(100 BYTE), vc100 VARCHAR2(100 CHAR), cb100 CHAR(100 BYTE), cc100 CHAR(100 CHAR));
> COLUMN data_type FORMAT A20;
> SET LINE 300
> SELECT column_name,data_type,data_length,
char_col_decl_length,char_length,char_used
FROM user_tab_columns WHERE table_name='MY_STRINGS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
----------- --------- ----------- -------------------- ----------- -
VB100 VARCHAR2 100 100 100 B
VC100 VARCHAR2 400 400 100 C
CB100 CHAR 100 100 100 B
CC100 CHAR 400 400 100 C

Note from the Author or Editor:
change "three bytes" to "four bytes"

On Oracle 11.1.0.7 Linux 32 bit with the file downloaded.
I got overflow in the four cases.
ERROR at line 1:
ORA-01426: numeric overflow

That was probably due to implicit convertion
(The loop index was probably also a PLS_INTEGER).
May you have a 64 bits system so integer have a big range.

By changing the loop by:
--pls_test: cnt PLS_INTEGER := 1;
LOOP
int1 := int1 + int2 * cnt;
cnt := cnt + 1;
EXIT WHEN cnt>iterations;
END LOOP;

--simple_test: cnt SIMPLE_INTEGER := 1;
LOOP
int1 := int1 + int2 * cnt;
cnt := cnt + 1;
EXIT WHEN cnt>iterations;
END LOOP;

pls_test still have exception.
simple_test result was:
123456789 iterations had run time of:+000000000 00:00:07.693335000
123456789 iterations had run time of:+000000000 00:00:00.711185000

So native of 11 times faster for SIMPLE_INTEGER. (+1081% faster)

By changing loop again to avoid overflow.
int1 := int1 + int2 * cnt / cnt;

output:
123456789 iterations had run time of:+000000000 00:01:15.499661000
123456789 iterations had run time of:+000000000 00:01:21.833653000

123456789 iterations had run time of:+000000000 00:00:59.324734000
123456789 iterations had run time of:+000000000 00:00:56.912449000

In my case PLS_INTEGER was faster that SIMPLE_INTEGER in 'interpreted'
But PLS_INTEGER was slower that SIMPLE_INTEGER in 'native'.
Native was always faster that interpreted.

Note from the Author or Editor:
Agree that the file simple_integer_demo.sql does not run as supplied in the sample file. This will have an impact both on the text of pp 239-240 and on the examples.

In fact, I believe it would be better to replace the lines that read
int1 := int1 + int2 * cnt;
with
int1 := int1 + int2;
because otherwise the perf test is muddied by the cnt variable, which I believe is defined by the language to be PLS_INTEGER, causing Oracle to do some implicit conversion(s).

In the above case, on my machine I get the following:

123456789 iterations had run time of:+000000000 00:00:07.026167000
123456789 iterations had run time of:+000000000 00:00:06.750064000
123456789 iterations had run time of:+000000000 00:00:01.766409000
123456789 iterations had run time of:+000000000 00:00:00.725167000

In any event, we definitely need to amend the example.

SF - for OPP6 I will remove the / cnt, and put in the timings above.

The BULK COLLECT clause is missing in the statement:

SELECT favourite_colors INTO l_colors ...

Note from the Author or Editor:
change this

SELECT favorite_colors INTO l_colors

to

SELECT favorite_colors BULK COLLECT INTO l_colors

"... raises the SUBSCRIPT_BEYOND_LIMIT exception."

For correctness, as well as consistency with other "Exceptions possible" paragraphs, should be:

"... raises the SUBSCRIPT_OUTSIDE_LIMIT predefined exception."

Proof:
1. From the standard package:
COLLECTION_IS_NULL exception;
pragma EXCEPTION_INIT(COLLECTION_IS_NULL , '-6531');

SUBSCRIPT_OUTSIDE_LIMIT exception;
pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-6532');

SUBSCRIPT_BEYOND_COUNT exception;
pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-6533');

2. Try these:
exec RAISE SUBSCRIPT_BEYOND_LIMIT
exec RAISE SUBSCRIPT_OUTSIDE_LIMIT

-- Note the ORA-06532 error, and compare with the pragma, above.
DECLARE TYPE vat IS VARRAY(2) OF PLS_INTEGER;
l_vat vat := vat(1,2);
BEGIN
l_vat.EXTEND;
END;
/

'In this example, I used both WRITE and WRITEAPPEND soley to demonstrate
the use of both procedures. because my LOB had no data to begin with, I
could have done all the work using only WRITEAPPEND.

I believe the last 'WRITEAPPEND' should say 'WRITE'

Note from the Author or Editor:
Correct. Change "only WRITEAPPEND" to "only WRITE"

"The syntax for this statement is:
LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];"


In Oracle 11g R1, LOCK TABLE has new syntax that lets you specify
the maximum number of seconds the statement should wait to obtain a DML lock on the table.

Note from the Author or Editor:
REPLACE

LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];

WITH

LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT | WAIT numseconds ]

...AND REPLACE

If you leave
out the NOWAIT keyword, the database waits until the table is available (and there is
no set limit on how long the database will wait).

WITH

Beginning in Oracle Database 11g Release 1, you can set the number of seconds to wait by using the WAIT clause. If you want to wait forever, leave out both NOWAIT and WAIT.

You said 4GB for CLOB but I am prety sure you said 8T to 128T before.

Note from the Author or Editor:
Change this

CLOB, whose maximum length is 4GB

to

CLOB.

On page 582 of this book, near the top, it says that an OUT parameter is
Write-only. This is incorrect; An OUT parameter can be read as well as
written to, as a simple test will prove.

On the next page, 583, it says "You cannot assign an OUT parameter's value
to another variable or even use it in a reassignment to itself."
This too is incorrect. You can assign the parameter's value to another
variable -- once again, as a simple test will prove.

This procedure works:
PROCEDURE Parse_HC
(p_timestamp_string IN VARCHAR2,
p_timestamp_date OUT DATE)
AS

v_date DATE;

BEGIN
p_timestamp_date := TO_DATE(p_timestamp_string, 'YYMMDDHH24MI');
v_date := p_timestamp_date;

END Parse_HC;

Note from the Author or Editor:
Will be fixed in 6th edition.

Paragraph read as -

With the following total_sales example, PL/SQL associates the first actual parameter,
:order.company_id, with the first formal parameter, company_id_in. It then associates
the second actual parameter, N, with the second formal parameter, status_in:
new_sales := total_sales (:order.company_id, 'N');

Not understood why ':' is used prefixing, order.company_id? In the next page same under the heading - Named Notation same example is used without ':'. I believe its used by mistake.

Note from the Author or Editor:
Change

:order.company_id

to

l_company_id

in both paragraph and code.

Paragraph read as -

With the following total_sales example, PL/SQL associates the first actual parameter,
:order.company_id, with the first formal parameter, company_id_in. It then associates
the second actual parameter, N, with the second formal parameter, status_in:
new_sales := total_sales (:order.company_id, 'N');

Not understood why ':' is used prefixing, order.company_id? In the next page same under the heading - Named Notation same example is used without ':'. I believe its used by mistake.

Note from the Author or Editor:
Change

:order_company_id

to

l_company_id

in both the paragraph and the code.

In the body of "total_sales" function,
CLOSE tot_cur;
is missing

Note from the Author or Editor:
Please insert after FETCH and before RETURN line, at same indentation:

CLOSE tot_cur;

Paragraph read as -

I would like to return a nested table based on the table definition itself. That is, I would
like it to be defined as follows:

TYPE tickertype_nt IS TABLE of tickertype%ROWTYPE;

I believe author(Steve) intended to write it as -


TYPE tickertype_nt IS TABLE of tickertable%ROWTYPE;

Note from the Author or Editor:
Correct! PLease change this line:

TYPE tickertype_nt IS TABLE of tickertype%ROWTYPE;

to

TYPE tickertype_nt IS TABLE of tickertable%ROWTYPE;

Code written in book as -
DECLARE
onebook book_info.bytitle_cur%ROWTYPE;
BEGIN
OPEN book_info.bytitle_cur ('%PL/SQL%');
LOOP
EXIT WHEN book_info.bytitle_cur%NOTFOUND;
FETCH book_info.bytitle_cur INTO onebook;
book_info.display (onebook);
END LOOP;
CLOSE book_info.bytitle_cur;
END;

In the above code -

1) Is not it a good practice to have Fetch first and then exit statement in the loop? For Example, lets say there is only one record retreived by the cursor then it will be displayed twice as fetching no rows in second attempt(or when the cursor pointer move past the last row ) is not going to nullify the last record values fetched.

I believe author not meant to print the last record twice!

2) Other way is to have first fetch statement just after the opening of cursor before loop. This is just my view it depends upon author, how he want to correct it.

Note from the Author or Editor:
Move the FETCH line above the EXIT line in the block of code referenced by the reader.

In Book - Under description column last sequence of sql statements-

INSERT INTO to_table
VALUES (1);
UPDATE to_table
SET col1 10;
DELETE to_table;
---
it should be

SET col1 = 10;

'=' missing.

Note from the Author or Editor:
change SET col1 10

to

SET col1 = 10

n Book - If a trigger raises an exception, that part of the transaction is rolled back.
--
To be precise it should be - If a trigger raises an un-handeled exception, that part of the transaction is rolled back.

Please consider following below code for your reference -

1) create table test_123( n number);

2) create or replace trigger trig_test_123_after_insert
after insert on test_123
for each row
begin
raise_application_error(-20005,'delibrate');

end;

3) begin
insert into test_123 values (123);
exception
when others then
null;
end;

select * from test_123;
----
Other scenario

4) create or replace trigger trig_test_123_after_insert
after insert on test_123
for each row
begin
raise_application_error(-20005,'delibrate');
exception
when others then
null;
end;

5) begin
insert into test_123 values (123);
exception
when others then
null;
end;

select * from test_123;

Note from the Author or Editor:
Very minor. Please change the first bullet point at bottom of page to:

If a trigger terminates with an unhandled exception, then the statement that caused the trigger to fire is rolled back.

Code for creation an editions is wrong:
CREATE_EDITION_NEW_HR_PATCH_NAMEFORMAT
/

should be
CREATE EDITION NEW_HR_PATCH_NAMEFORMAT;
/

names and mileage collection near to be initialized before EXTEND.

You could also remove the space between 'major_polluters_cur' and '%ROWCOUNT' but that not seem to cause compile error.

Note from the Author or Editor:
change these lines:

names names_t;
mileages mileage_t;

to

names names_t := names_t();
mileages mileage_t := mileage_t();

and change this

major_polluters_cur %ROWCOUNT

to

major_polluters_cur%ROWCOUNT

First, line 23 not appear in the output below.
Instead of EXEC bulk_exceptions should be /
Message 1 should be iteration 3 instead of 2 and message should be cannot update to NULL.
Message 2 should be iteration 5 instead of 4 and message value to large.
PS.: Message seem to be in inverse order with wrong iteration number.

Note from the Author or Editor:
Please change the four lines under SQL> EXEC to this:

Error 1 occurred during iteration 3 updating name to BIGBIGGERBIGGEST
Oracle error is ORA-01407: cannot update () to NULL

Error 2 occurred during iteration 5 updating name to
Oracle error is ORA-01401: inserted value too large for column

"If you try to open with append, the file must already exist...if not..INVALID_OPERATION will be raised".

Oracle documentation: "If you try to open a file specifying 'a' or 'ab' for open_mode but the file does not exist, the file is created in write mode."

(http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_file.htm#i1003326)

I tried it, opening a non-existent file with either W or A is fine.
Perhaps I'm missing something, great book though, cheers.

Note from the Author or Editor:
Confirmed and fixed in 6th edition

"There are essentially three different types of key management.
? A single key for the entire database.
? A single key for each row of tables with encrypted data.
? A combination of the above two."


There are essentially four different types of key management.
? A single key for the entire database.
? A single key for EACH TABLE with encrypted data.
? A single key for each row of tables with encrypted data.
? A combination of the above ...

Oracle Database Advanced Security Administrator's Guide (11.2),
page 3-2:
TDE Column Encryption
...This master encryption key is used to encrypt THE TABLE KEY, which in turn is used to encrypt and decrypt data in the table column...

Note from the Author or Editor:
In addition to the possibility of using a table-level encryption key, it is also possible to use a column-level key. Adding these two extra approaches would require some extra paragraphs.

SF - I have sent this errata to Arup, who "owns" this chapter. It will be addressed in OPP6

Instead of:

Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, policy_name:

should be, I think:

Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, statement_types:

(i.e. replace policy_name with statement_types)

Note from the Author or Editor:
Instead of:

Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, policy_name:

should be, I think:

Starting with Oracle Database 10g, because FGA can be applied to regular DML, as well, I can define the specific statements on which the policy should be effective, via a new parameter, statement_types:

(i.e. replace policy_name with statement_types)

$ENDIF should be $END

Note from the Author or Editor:
Change as directed.

The variables returned are labelled as 'TIMEZONE' and 'TIMEZONE WITH LOCAL TIMESTAMP',while they should be 'TIMESTAMP' and 'TIMESTAMP WITH LOCAL TIMEZONE'. It's just a typo, but even if the correct sentence can be inferred from the context it's nonetheless an unpleasant thing to see in such a prestigious book, worth every penny.

Note from the Author or Editor:
Correct and will be fixed in OPP6.

The paragraph starting with TIMEZONE and TIMEZONE WITH should be

TIMESTAMP and TIMESTAMP WITH

"'Private' attributes, variables, constants, and methods - in 11g"


Oracle 11g supports neither private attributes nor private methods.
(See page 1116:
However, because Oracle supports neither private attributes nor private methods, the incremental sacrifice here is small.)

Note from the Author or Editor:
Remove the check for "Private" under 11g.

"By the way, you can drop a type using the statement:
DROP TYPE typename [ FORCE ];
Use the FORCE option (available only in Oracle Database 11g Release 2)..."



"DROP TYPE typename FORCE" exists even in 8.0.
New feature in Oracle Database 11g Release 2 is this:
CREATE OR REPLACE TYPE statements now have a FORCE option,
which enables you to replace the type even if it has type dependents.

Note from the Author or Editor:
Delete the text:

(available only in Oracle Database 11g Release 2)