Oracle 12c PL/SQL

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&gt; 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&gt; DECLARE
  ...
 19    lv_stooges(lv_stooges.COUNT) := 'Curly';
  ...
 14  END;
 15  /

Page 224:

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

  lv_stooges(lv_stooges.COUNT) := NULL;
                                  *
ERROR at line 18:
ORA-06550: line 18, column 41:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored

and lower in the page:

  lv_stooges  SQL_VARRAY := sql_varray('Moe','Larry','Shemp');

Page 225:

Line 19 should assign should refer to a sql_table not a ~~sql_varray~~ variable. A corrected line 2 should look like this:

SQL&gt; SELECT   column_value AS "Dúnedain"
  2  FROM     TABLE(sql_table('Aragorn','Faramir','Boromir'))
  3  ORDER BY 1;

Page 241:

The syntax to define an associative array should appear as:

~~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:

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

Chapter 7 : Error Management

Page 271:

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:

utl_call_stack.~~backtrace_line~~(utl_call_stack.backtrace_depth)

it should call the error_number function in the package:

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

~~[{EDITIONABLE | NONEDITIONABLE}]~~ FUNCTION function_name

it should be:

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&gt; 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  /

Chapter 8’s pipelined.sql script ↓

The pipelined.sql script demonstrates the pipelined function behaviors from Chapter 8. It shows how you would test the pipelined programs.

/* ================================================================
||   Program Name: pipelined.sql
||   Date:         2013-07-25
||   Updated:      2014-12-04
||   Book:         Oracle Database 12c PL/SQL Programming
||   Chapter #:    Chapter 8
||   Author Name:  Michael McLaughlin
|| ----------------------------------------------------------------
||   Contents:
||   ---------
||   This script demonstrates pipelined functions.
|| ================================================================*/
 
-- Enable PL/SQL printing.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Remove environment statements for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000
 
-- Create a single column pipelined function.
CREATE OR REPLACE FUNCTION pipelined_numbers
RETURN NUMBERS
PIPELINED IS
  list NUMBERS := numbers(0,1,2,3,4,5,6,7,8,9);
BEGIN
  FOR i IN 1..list.LAST LOOP
    PIPE ROW(list(i));
  END LOOP;
  RETURN;
END;
/
 
-- Create a pipelined package specification.
CREATE OR REPLACE PACKAGE pipelined IS
  /* Declare a PL/SQL record and collection type. */
  TYPE account_record IS RECORD
  ( account     VARCHAR2(10)
  , full_name   VARCHAR2(42));
  TYPE account_collection IS TABLE OF account_record;
 
  /* Declare a pipelined function. */
  FUNCTION pf RETURN account_collection PIPELINED;
END pipelined;
/
 
-- Create a working pipelined package body.
CREATE OR REPLACE PACKAGE BODY pipelined IS
  -- Implement a pipelined function.
  FUNCTION pf
  RETURN account_collection
  PIPELINED IS
    /* Declare a collection control and collection variable. */
    counter NUMBER := 1;
    account ACCOUNT_COLLECTION := account_collection();
 
    /* Define a cursor. */
    CURSOR c IS
      SELECT   m.account_number
      ,        c.last_name || ', '||c.first_name full_name
      FROM     member m JOIN contact c ON m.member_id = c.member_id
      ORDER BY c.last_name, c.first_name;
  BEGIN
    FOR i IN c LOOP
      /* Allot space and add values to collection. */
      account.EXTEND;
      account(counter).account   := i.account_number;
      account(counter).full_name := i.full_name;
 
      /* Assign the collection element to the PIPE. */
      PIPE ROW(account(counter));
      counter := counter + 1;
    END LOOP;
    RETURN;
  END pf;
END pipelined;
/
 
-- Query the results from the pipelined package's function.
SELECT * FROM TABLE(pipelined.pf);
 
-- Create a pipelined package body that fails.
CREATE OR REPLACE PACKAGE BODY pipelined IS
  -- Implement a pipelined function.
  FUNCTION pf
  RETURN pipelined.account_collection
  PIPELINED IS
    /* Declare a collection control and collection variable. */
    counter NUMBER := 1;
    account ACCOUNT_COLLECTION := account_collection();
 
    /* Define a cursor. */
    CURSOR c IS
      SELECT   m.account_number
      ,        c.last_name || ', '||c.first_name full_name
      FROM     member m JOIN contact c ON m.member_id = c.member_id
      ORDER BY c.last_name, c.first_name;
  BEGIN
    FOR i IN c LOOP
      /* Allot space and add values to collection. */
      account.EXTEND;
      account(counter) := i;
 
      /* Assign the collection element to the PIPE. */
      PIPE ROW(account(counter));
      counter := counter + 1;
    END LOOP;
    RETURN;
  END pf;
END pipelined;
/
 
-- Create a working pipelined package body.
CREATE OR REPLACE PACKAGE BODY pipelined IS
  -- Implement a pipelined function.
  FUNCTION pf
  RETURN account_collection
  PIPELINED IS
    /* Declare a collection control and collection variable. */
    counter NUMBER := 1;
    account ACCOUNT_COLLECTION := account_collection();
 
    /* Define a cursor. */
    CURSOR c IS
      SELECT   m.account_number
      ,        c.last_name || ', '||c.first_name full_name
      FROM     member m JOIN contact c ON m.member_id = c.member_id
      ORDER BY c.last_name, c.first_name;
  BEGIN
    FOR i IN c LOOP
      /* Allot space and add values to collection. */
      account.EXTEND;
      account(counter).account   := i.account_number;
      account(counter).full_name := i.full_name;
 
      /* Assign the collection element to the PIPE. */
      PIPE ROW(account(counter));
      counter := counter + 1;
    END LOOP;
    RETURN;
  END pf;
END pipelined;
/
 
-- Create a stand alone function that uses the pipelined function.
CREATE OR REPLACE FUNCTION pf
RETURN pipelined.account_collection
PIPELINED IS
    -- Declare a collection control variable and collection variable.
    counter NUMBER := 1;
    account PIPELINED.ACCOUNT_COLLECTION := pipelined.account_collection();
 
    -- Define a cursor.
    CURSOR c IS
      SELECT   m.account_number
      ,        c.last_name || ', '||c.first_name full_name
      FROM     member m JOIN contact c ON m.member_id = c.member_id
      ORDER BY c.last_name, c.first_name;
  BEGIN
    FOR i IN c LOOP
      account.EXTEND;
      account(counter).account   := i.account_number;
      account(counter).full_name := i.full_name;
      PIPE ROW(account(counter));
      counter := counter + 1;
    END LOOP;
    RETURN;
  END pf;
/
 
-- Query the results from the pipelined package's function.
SELECT * FROM TABLE(pf);
 
-- Enable server output.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Create a procedure to receive a collection and print values.
CREATE OR REPLACE PROCEDURE read_pipe
( pipe_in pipelined.account_collection ) IS
BEGIN
  FOR i IN 1..pipe_in.LAST LOOP
    dbms_output.put(pipe_in(i).account);
    dbms_output.put(pipe_in(i).full_name);
  END LOOP;
END read_pipe;
/
 
-- Show that a PL/SQL context fails.
EXECUTE read_pipe(pf);
 
-- Show that a SQL context succeeds.
BEGIN
  FOR i IN (SELECT * FROM TABLE(pf) ORDER BY 1) LOOP
    dbms_output.put_line(i.account||' '||i.full_name);
  END LOOP;
END;
/

/* ================================================================ || Program Name: pipelined.sql || Date: 2013-07-25 || Updated: 2014-12-04 || Book: Oracle Database 12c PL/SQL Programming || Chapter #: Chapter 8 || Author Name: Michael McLaughlin || ---------------------------------------------------------------- || Contents: || --------- || This script demonstrates pipelined functions. || ================================================================*/ -- Enable PL/SQL printing. SET SERVEROUTPUT ON SIZE UNLIMITED -- Remove environment statements for debugging script. SET ECHO ON SET FEEDBACK ON SET PAGESIZE 49999 SET SERVEROUTPUT ON SIZE 1000000 -- Create a single column pipelined function. CREATE OR REPLACE FUNCTION pipelined_numbers RETURN NUMBERS PIPELINED IS list NUMBERS := numbers(0,1,2,3,4,5,6,7,8,9); BEGIN FOR i IN 1..list.LAST LOOP PIPE ROW(list(i)); END LOOP; RETURN; END; / -- Create a pipelined package specification. CREATE OR REPLACE PACKAGE pipelined IS /* Declare a PL/SQL record and collection type. */ TYPE account_record IS RECORD ( account VARCHAR2(10) , full_name VARCHAR2(42)); TYPE account_collection IS TABLE OF account_record; /* Declare a pipelined function. */ FUNCTION pf RETURN account_collection PIPELINED; END pipelined; / -- Create a working pipelined package body. CREATE OR REPLACE PACKAGE BODY pipelined IS -- Implement a pipelined function. FUNCTION pf RETURN account_collection PIPELINED IS /* Declare a collection control and collection variable. */ counter NUMBER := 1; account ACCOUNT_COLLECTION := account_collection(); /* Define a cursor. */ CURSOR c IS SELECT m.account_number , c.last_name || ', '||c.first_name full_name FROM member m JOIN contact c ON m.member_id = c.member_id ORDER BY c.last_name, c.first_name; BEGIN FOR i IN c LOOP /* Allot space and add values to collection. */ account.EXTEND; account(counter).account := i.account_number; account(counter).full_name := i.full_name; /* Assign the collection element to the PIPE. */ PIPE ROW(account(counter)); counter := counter + 1; END LOOP; RETURN; END pf; END pipelined; / -- Query the results from the pipelined package's function. SELECT * FROM TABLE(pipelined.pf); -- Create a pipelined package body that fails. CREATE OR REPLACE PACKAGE BODY pipelined IS -- Implement a pipelined function. FUNCTION pf RETURN pipelined.account_collection PIPELINED IS /* Declare a collection control and collection variable. */ counter NUMBER := 1; account ACCOUNT_COLLECTION := account_collection(); /* Define a cursor. */ CURSOR c IS SELECT m.account_number , c.last_name || ', '||c.first_name full_name FROM member m JOIN contact c ON m.member_id = c.member_id ORDER BY c.last_name, c.first_name; BEGIN FOR i IN c LOOP /* Allot space and add values to collection. */ account.EXTEND; account(counter) := i; /* Assign the collection element to the PIPE. */ PIPE ROW(account(counter)); counter := counter + 1; END LOOP; RETURN; END pf; END pipelined; / -- Create a working pipelined package body. CREATE OR REPLACE PACKAGE BODY pipelined IS -- Implement a pipelined function. FUNCTION pf RETURN account_collection PIPELINED IS /* Declare a collection control and collection variable. */ counter NUMBER := 1; account ACCOUNT_COLLECTION := account_collection(); /* Define a cursor. */ CURSOR c IS SELECT m.account_number , c.last_name || ', '||c.first_name full_name FROM member m JOIN contact c ON m.member_id = c.member_id ORDER BY c.last_name, c.first_name; BEGIN FOR i IN c LOOP /* Allot space and add values to collection. */ account.EXTEND; account(counter).account := i.account_number; account(counter).full_name := i.full_name; /* Assign the collection element to the PIPE. */ PIPE ROW(account(counter)); counter := counter + 1; END LOOP; RETURN; END pf; END pipelined; / -- Create a stand alone function that uses the pipelined function. CREATE OR REPLACE FUNCTION pf RETURN pipelined.account_collection PIPELINED IS -- Declare a collection control variable and collection variable. counter NUMBER := 1; account PIPELINED.ACCOUNT_COLLECTION := pipelined.account_collection(); -- Define a cursor. CURSOR c IS SELECT m.account_number , c.last_name || ', '||c.first_name full_name FROM member m JOIN contact c ON m.member_id = c.member_id ORDER BY c.last_name, c.first_name; BEGIN FOR i IN c LOOP account.EXTEND; account(counter).account := i.account_number; account(counter).full_name := i.full_name; PIPE ROW(account(counter)); counter := counter + 1; END LOOP; RETURN; END pf; / -- Query the results from the pipelined package's function. SELECT * FROM TABLE(pf); -- Enable server output. SET SERVEROUTPUT ON SIZE UNLIMITED -- Create a procedure to receive a collection and print values. CREATE OR REPLACE PROCEDURE read_pipe ( pipe_in pipelined.account_collection ) IS BEGIN FOR i IN 1..pipe_in.LAST LOOP dbms_output.put(pipe_in(i).account); dbms_output.put(pipe_in(i).full_name); END LOOP; END read_pipe; / -- Show that a PL/SQL context fails. EXECUTE read_pipe(pf); -- Show that a SQL context succeeds. BEGIN FOR i IN (SELECT * FROM TABLE(pf) ORDER BY 1) LOOP dbms_output.put_line(i.account||' '||i.full_name); END LOOP; END; /

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&gt; 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

~~[{EDITIONABLE | NONEDITIONABLE}]~~ PROCEDURE procedure_name

it should be:

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&gt; 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&gt; 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&gt; 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: