Errata for Oracle PL/SQL Programming

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 &#8722;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 &#8722;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)