MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle 12c PL/SQL

with 19 comments

While you try to eliminate all errors when you write a book, some exceptions remain and some new opportunities present themselves. I think we did a great job on this book and the errors are minimal, but readers bring new insights and rereading what you’ve written brings fresh ideas. This errata includes corrections of mistakes, suggestions for change, and improvements to the base text. If the book is successful, I’m sure they’ll make it into the next edition.

If you’re reading or read the book, please feel free to let me know of any uncaught error or suggestion that you may have. Thanks, Michael

Errata:
(Acknowledgment and corrections to Oracle Database 12c PL/SQL Programming)


Change Key:

removed text

added text


Chapter 1 : Oracle PL/SQL Development Overview


 


Chapter 2 : New Features


 


Chapter 3 : PL/SQL Basics


Page 50:

The ending clause of the third bullet should be removed as shown:

  • PL/SQL supports single- and multiple-line comments, which are assumed to be numeric unless enclosed in single quotes.

Page 53:

A preposition was inadvertently deleted from the following sentence in the second paragraph:

What you should learn here is that you should never make dynamic assignments in a declaration block.

Page 53:

The third sentence in the fourth paragraph was missing “a value to” phrase, as shown below:

Functions must return a value to a variable when they’re right operands.

Page 54:

The reference to the data type in the first example after the sidebar should refer to an INTEGER rather than a NUMBER:

For example, suppose you attempt to assign a real number of 4.67 to a variable with a NUMBER an INTEGER data type, like this:

Page 55:

The following sentence in the paragraph between the bullet points has an extra only in the sentence:

CURSOR parameters are pass-by-value, or IN-only mode only variables.

Page 97:

The book uses a pass-by-value and pass-by-reference analogy to describe how parameter passing works from the perspective of the calling program, but adding a side bar on the actual mechanics would have been a good idea. Here’s my suggestion for that sidebar with a light-gray background, like those in the book:


Passing what, to where, and why?

When you write PL/SQL programs, you typically care more about the business logic then how Oracle compiles and runs them. That’s why the concepts of pass-by-value and pass-by-reference help explain how you work with stored functions and procedures.

At the PL/SQL level, you see two options:

  • You call a pass-by-value parameter (IN mode) with a variable or literal value. From the perspective of the calling program, you pass a copy of a value to subprogram and never expect to see it back. Hence, you pass a value from the calling program to the called program.
  • You call a pass-by-reference parameter (IN OUT or OUT mode) with only a variable. More or less, you pass a reference to the call parameter. The reference lets the subprogram change the value of the external variable.

While I use this to describe how to work with subprograms, it is a simplification. The PL/SQL engine works differently. A pass-by-value parameter doesn’t actually get a copy because it receives an immutable (read-only) reference to an external variable or value. Likewise, a pass-by-reference parameter holds a copy of (default) or reference to an external variable. If the PL/SQL engine chooses to pass a copy, the value of the copy overwrites any value held by the original external variable at the completion of the subprogram. If the PL/SQL engine passes a reference, the subprogram can write a change to the external variable directly.


Chapter 4 : Language Fundamentals



Chapter 5 : Control Structure


Page 159:

The last sentence in the description of the MEMBER OF is exits when it should be exists, as shown below:

It returns true when the element exits exists in a collection and returns false when it doesn’t.

Page 170:

The two new predefined inquiry directives where missing a key element of their identifier name:

  • $$PLSQL_UNIT_OWNER Returns the database user who owns the current program unit, and it is also a new predefined inquiry directive with Oracle Database 12c.
  • $$PLSQL_UNIT_TYPE Returns the current program unit’s type, and it is also a new predefined inquiry directive with Oracle Database 12c.

Page 174:

Line 2 should assign a 0 not a 1 to the lv_counter variable. The corrected code block should look like this:

SQL> DECLARE
  2    lv_counter  NUMBER := 0;
  3  BEGIN
  ...
 14  END;
 15  /

Page 193:

The last sentence in the third paragraph refers to line 9 when it should refer to 6, as shown below:

The PL/SQL-only alternative to coupling the lv_record variable to a cursor’s row structure is to couple the lv_record to a table/view definition or to an explicit record type (check the “Records” section in Chapter 4 for a complete example). If we create an item_record record type in the declaration block, we can define the lv_record variable on line 9 6, like this:

Page 197:

The item variable should be item_record to match the preceding example. The corrected code block should look like this:

 13     FOR i IN c (lv_lowend, lv_highend) LOOP
 14       item_record := i;
 15       dbms_output.put_line('Title ['||item_record.title||']');
 16     END LOOP;


Chapter 6 : Collections


Page 223:

Line 19 should assign should refer to a lv_stooges not a lv_collection variable. A corrected line 19 should look like this:

SQL> DECLARE
  ...
 19    lv_stooges(lv_stooges.COUNT) := 'Curly';
  ...
 14  END;
 15  /

Page 241:

The syntax to define an associative array should appear as:

osborneBullet

CREATE OR REPLACE TYPE type_name AS IS TABLE OF base_type [ NOT NULL ]
INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];

or, it should appear as:

osborneBullet

CREATE OR REPLACE TYPE type_name AS IS TABLE OF base_type [ NOT NULL ]
INDEX BY key_type;


Chapter 7 : Error Management


Page 241:

The syntax to example to find the error number with the utl_call_stack function refers to the wrong backtrace_line function in the package:

osborneBullet

utl_call_stack.backtrace_line(utl_call_stack.backtrace_depth)

it should call the error_number function in the package:

osborneBullet

utl_call_stack.error_number(utl_call_stack.backtrace_depth)


Chapter 8 : Functions and Procedures


Page 294:

The last outline bullet reads as Pass-by-reference functions when it should read as Pass-by-reference procedures, as shown below:

◼ Pass-by-reference functions procedures.

Page 307:

The prototype specification should include CREATE [OR REPLACE] rather than [{EDITIONABLE | NONEDITIONABLE}], as shown below

osborneBullet

[{EDITIONABLE | NONEDITIONABLE}] FUNCTION function_name

it should be:

osborneBullet

CREATE [OR REPLACE] FUNCTION function_name

Page 313-314:

Line 6 and line 9 should use account_collection type not an account_table type. The corrected package specification should be:

SQL> CREATE OR REPLACE PACKAGE pipelined IS
  2    /* Declare a PL/SQL record and collection type. */
  3    TYPE account_record IS RECORD
  4    ( account	VARCHAR2(10)
  5    , full_name	VARCHAR2(42));
  6    TYPE account_collection IS TABLE OF account_record;
  7  
  8    /* Declare a pipelined function. */
  9    FUNCTION pf RETURN account_collection PIPELINED;
 10  END pipelined;
 11  /

Page 315:

The second sentence in the fourth paragraph refers to line 19 when it should refer to line 21, as shown below:

Varray and table collections require you to allocate space before adding elements to a collection. The EXTEND method on line 19 21 allocates space for one element and then values are assigned to components of that indexed element. As discussed, they may be assigned by field element or by record through the cursor pointer, or iterator of a cursor FOR loop.

Page 316:

Line 2 and 6 should refer to a list variable and they erroneously refer to a lv_list variable. The modified anonymous block should be:

SQL> DECLARE
  2    list  TYPE_DEFS.PLSQL_TABLE;
  3  BEGIN
  4    list := implicit_convert;
  5    FOR i IN (SELECT   column_value
  6              FROM     TABLE(list)) LOOP
  7      dbms_output.put_line(i.column_value);
  8    END LOOP;
  9  END;
 10  /

Page 339:

The prototype specification should include CREATE [OR REPLACE] rather than [{EDITIONABLE | NONEDITIONABLE}], as shown below

osborneBullet

[{EDITIONABLE | NONEDITIONABLE}] PROCEDURE procedure_name

it should be:

osborneBullet

CREATE [OR REPLACE] PROCEDURE procedure_name

Page 341:

The sentence in the third paragraph refers to an adding_contact procedure when it should refer to an adding_avatar procedure, as shown below:

The adding_contact adding_avatarprocedure shows you how to use a pass-by-value procedure to manage multiple DML statements across a single transaction scope:

Page 341:

The comment on line 17 should refer to the episode table not the avatar table, as shown:

SQL> CREATE OR REPLACE PROCEDURE adding_avatar
  ...
 17    /* Insert row into episode. */
 18    INSERT INTO episode (avatar_id, episode_name)
 19    VALUES (lv_avatar_id, pv_episode_name);
  ...
 26  END;
 15  /


Chapter 9 : Packages


Page 352:

The not_overloading name at the end of the overloading package on line 6 should be overloading, which matches the package name. The corrected code block should look like this:

SQL> CREATE OR REPLACE PACKAGE overloading IS
  2    FUNCTION adding (a NUMBER, b NUMBER) RETURN NUMBER;
  3    FUNCTION adding (a VARCHAR2, b NUMBER) RETURN NUMBER;
  4    FUNCTION adding (a NUMBER, b VARCHAR2) RETURN NUMBER;
  5    FUNCTION adding (a VARCHAR2, b VARCHAR2) RETURN BINARY_INTEGER;
  6  END overloading;
  7  /

Page 361:

The pv_unprotected variable name on line 3 of the shared package specification should be lv_unprotected. The corrected code block should look like this:

SQL> CREATE OR REPLACE PACKAGE shared IS
  2    lv_unprotected   CONSTANT NUMBER := 1;
  3    lv_unprotected            NUMBER := 1;
  4  END shared;
  5  /


Chapter 10 : Large Objects


 


Chapter 11 : Object Types


 


Chapter 12 : Triggers


 


Chapter 13 : Dynamic SQL


 


Appendix A : Oracle Database Primer


Page 653:

There was a suggestion that the appendix should include a table that describes the CDB_, DBA_, ALL_, and USER_ views. So, here’s a table with that information:


Administrative Views

You can find the list of administrative views by querying the DICTIONARY view. More or less, you have four types of administrative views in a multitenant Oracle Database 12c. The CDB_ views don’t exist in non-multitenant databases. They are:

  • A CDB_ view displays all relevant information in the a multitenant database, including pluggable databases. CDB_ views are intended only for administrators. They can be accessed only by the super users, like sys and system.
  • A DBA_ view displays all relevant information in the a database, either a non-multitenant database or pluggable database in a multitenant database. DBA_ views are intended only for administrators. They can be accessed only by users that enjoy the SELECT ANY TABLE privilege. The SELECT ANY TABLE privilege is assigned to the DBA role when you install a database.
  • An ALL_ view displays all the information accessible to the current user, including information from the current user’s schema as well as information from objects in other schemas. The key determinant of what can be seen are the grants of privilege through a direct grant of a privilege or role.
  • A USER_ view displays all the information from the schema of the current user. The user has these by default and they require no special privileges.

Page 653:

A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

The views prefixed with ALL_ CDB_ and DBA_ are accessible only to the Oracle superusers: sys, system, and user-defined accounts granted superuser privileges.

Page 666:

A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

For example, there is no UNIQUE constraint visible in the USER_CONSTRAINTS administrative view of the database catalog or in the superuser views of ALL_ CDB_ and DBA_CONSTRAINTS.


Appendix B : SQL Primer


Page 705:

A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

The ALL_ CDB_ and DBA_ prefixed views are accessible only to the Oracle superusers: sys, system, and user-defined accounts.

Page 718:

A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

The case of table and column names is found in the USER_TAB_COLUMNS view or, if you enjoy DBA privileges, the ALL_TAB_COLUMNS CDB_TAB_COLUMNS and DBA_TAB_COLUMNS views.

Page 726:

A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

  • Joins between system-generated segment names don’t throw an error when matching unrelated system-generated return values found in the DBA_ or ALL_SEGMENTS CDB_SEGMENTS view.
  • Page 726:

    A cut-and-paste error uses an ALL_ when it should use a CDB_, as shown below:

  • The ALL_ CDB_ and DBA_ prefixes give you access to objects owned by others, and only superusers or administrative users have access privileges to these views.

  • Appendix C : SQL Built-in Functions


    Page 901:

    The SUBSTR function is missing from the Character Functions section and important. This should be in the book:

    SUBSTR Function

    The SUBSTR function lets you capture a substring of another string. The prototype is

    SUBSTR(character_string, POSITION, substring_length)

    The SUBSTR function finds a substring starting from a position in the string for a specified length. The INSTR function assists you in finding the position of a white space. Together, they let you find a substring.

    The following demonstrates the SUBSTR function:

    SQL> DECLARE
      2    comment  VARCHAR2(20) := 'Wookies rock!';
      3  BEGIN
      4    dbms_output.put_line(
      5        'Substring ['
      6      || SUBSTR( comment
      7               , INSTR(comment,' ') + 1
      8               , LENGTH(comment) - INSTR(comment, ' ') - 1)||']');
      9  END;
     10  /

    The SUBSTR function runs across lines 6 through 8. On line 7, the INSTR plus 1 finds the beginning of the second word. On line 8, the length of the string is subtracted from the occurrence of the white space minus 1. The minus 1 removes the exclamation from the substring, and leaves only:

    Substring [rocks]

    Appendix D : PL/SQL Built-in Packages and Types


     


    Appendix E : Regular Expression Primer


     


    Appendix F : Wrapping PL/SQL Code Primer


     


    Appendix G : PL/SQL Hierarchical Profiler Primer


     


    Appendix H : PL/SQL Reserved Words and Keywords


    Written by maclochlainn

    February 1st, 2014 at 2:57 am

    19 Responses to 'Oracle 12c PL/SQL'

    Subscribe to comments with RSS or TrackBack to 'Oracle 12c PL/SQL'.

    1. […] created an Errata for the Oracle Database 12c PL/SQL Programming […]

    2. On page 174 in chapter 5, the code at the bottom of the page, line 2 should be lv_counter NUMBER := 0; instead of being equal to 1.

      Sarah LaMont

      14 May 14 at 9:25 pm

    3. On page 241, the definition syntax of an associative array (2 places) show the keyword ‘AS‘ which only works in the schema-level declaration. It may help to clarify that the keyword ‘IS‘ must be used in the declaration of a PL/SQL programming unit.

      Chris Hepworth

      22 May 14 at 3:59 pm

    4. page 223
      line 19 of the example code
      lv_collection should be lv_stooges

      Chris Hepworth

      22 May 14 at 5:15 pm

    5. Chapter 5, page 197 – the second code block should read at lines 14 and 15:
      item_record := i;
      dbms_output.put_line('Title ['||item_record.title||']');

      This will match the block above where the c%ROWTYPE call is placed in the item_record variable.

      Chris Hepworth

      24 May 14 at 1:50 pm

    6. Chapter 7, Exception Stack Functions, p 281-287

      A comment was made in class that this section would benefit from a diagram of FILO stack handling.

      Chris Hepworth

      27 May 14 at 4:29 pm

    7. On page 550, I noticed that line 12 utilizes dbms_assert.simple_sql_name while the text below makes reference to this being dbms_assert.qualified_sql_name.

      Chris Hepworth

      10 Jul 14 at 3:11 pm

    8. Page 765 – Only a grammatical error

      At the bottom of the page, in the grayed-out box, the last sentence states:

      ‘Oracle Database 12c does allow you to overload a column of a base object type when you’ve implement the object type bodies with proper constructors’

      The word ‘implement’ should be ‘implemented’

      Lance Wilson

      16 Jul 14 at 8:33 am

    9. Page 53: “Functions must return a variable when they’re right operands.”

      I believe the word “variable” should be replaced by the word “value” in the above stmnt.

      Gabe Ventilla

      14 Jan 15 at 7:29 pm

    10. Page 54: “For example, suppose you attempt to assign a real number of 4.67 to a variable with a NUMBER data type, like this:…”

      NUMBER should be replaced by INTEGER to be consistent with the example that follows…

      Gabe Ventilla

      14 Jan 15 at 8:05 pm

    11. I’ve fixed this and the others you’ve found on pages 53 to 55.

      maclochlainn

      15 Jan 15 at 11:48 pm

    12. Within “Chapter 7 : Error Management” it says the error is on page 241. That page is in Chapter 6, which has its own error listed. What is the correct page number for the Chapter 7 error?

      RJB

      24 Oct 15 at 9:45 pm

    13. PG 340 in Chapter 8
      First bullet of “5 rules” for pass-by-VALUE procedures says “All Formal Parameters must be defined as write-only variables using the IN mode.”

      As defined on page 300 IN is read-only.

      RJB

      26 Oct 15 at 8:17 am

    14. Chapter 9 page 372

      The function ‘locked’ uses BOOLEAN as a return type.
      BOOLEAN isn’t supported as a return type (see page 713).

      RJB

      4 Nov 15 at 9:55 am

    15. On p1061 in appendix I, the answer for #2 appears to assume that the question asked about ELSEIF instead of elsif.

      William

      16 Nov 15 at 8:25 am

    16. It supports a BOOLEAN data type in a PL/SQL context. However, it doesn’t support a BOOLEAN data type in a SQL context.

      maclochlainn

      20 Nov 15 at 11:50 pm

    17. Reading on SafariBooksOnline. Chapter 2: Section “Table with Explicit VT Columns” has the image of a PL/SQL block that declares a cursor with OFFSET and ROWS FETCH FIRST clauses. This doesn’t seem to have anything to do with VT columns and doesn’t fit with the SELECT statement that follows.

      Joe

      10 Jul 17 at 3:38 pm

    18. Amazon sample book has the correct image that shows the create table example with VT columns. Maybe just notify Safari Books to link to the correct image file.

      Joe

      10 Jul 17 at 3:44 pm

    19. Chapter 3: Local Named Blocks: Line 15 of First PL/SQL Example
      Has “lv_local := ‘INNER’;” but lv_local isn’t a declared variable. Believe this is supposed to be lv_outer instead.

      Joe

      12 Jul 17 at 3:31 pm

    Leave a Reply