The magic of PL/SQL revealed in three questions

[This article was first published in Oracle Magazine in 2016 and has been updated for the latest version of Oracle Database. —Ed.]

To help users of PL/SQL build the cleanest, fastest interfaces, Oracle has built several wonderful, and often deceptively simple, features into the language.

As Bryn Llewellyn wrote in his “Why Use PL/SQL?” business brief, “Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use Oracle Database, the database is the persistence module. The tables and the SQL statements that manipulate them are the implementation details. The interface is expressed with PL/SQL.”

This article explores a few of these PL/SQL wonders through three quiz questions: Do you know when to anchor, when to loop, and when not to make?

Question 1: Anchored declarations

I execute the following statements:

CREATE TABLE plch_trees
(
   id              INTEGER,
   tree_name       VARCHAR2 (6),
   tree_location   VARCHAR2 (20)
)
/
BEGIN
   INSERT INTO plch_trees (id, tree_name, tree_location)
        VALUES (300, 'Ginkgo', 'China');
   COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree1 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree_name   VARCHAR2 (6);
BEGIN
   SELECT tree_name
     INTO l_tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree2 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree_name   plch_trees.tree_name%TYPE;
BEGIN
   SELECT tree_name
     INTO l_tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_show_tree3 (id_in IN INTEGER)
   AUTHID DEFINER
IS
   l_tree   plch_trees%ROWTYPE;
BEGIN
   SELECT tree_name
     INTO l_tree.tree_name
     FROM plch_trees
    WHERE id = id_in;
   DBMS_OUTPUT.put_line (l_tree.tree_name);
END;
/
CREATE OR REPLACE PROCEDURE plch_change_table
   AUTHID DEFINER
IS
BEGIN
   EXECUTE IMMEDIATE 'alter table plch_trees modify tree_name varchar2(10)';
   EXECUTE IMMEDIATE
      q'[
   BEGIN
      INSERT INTO plch_trees (id, tree_name, tree_location)
           VALUES (100, 'Black Oak', 'Eastern US');
      INSERT INTO plch_trees (id, tree_name, tree_location)
           VALUES (200, 'Tamarack', 'Europe');
      COMMIT;
   END;]';
END;
/

Which of the following choices display “Ginkgo” followed by “Black Oak” after execution?

Here’s answer A.

BEGIN
   plch_show_tree1 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree1 (100);
END;
/

This is answer B.

BEGIN
   plch_show_tree2 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree2 (100);
END;
/

And here’s answer C.

BEGIN
   plch_show_tree3 (300);
   plch_change_table;
END;
/
BEGIN
   plch_show_tree3 (100);
END;
/

PL/SQL supports anchored declarations, using %TYPE and %ROWTYPE. This lets PL/SQL directly express the intention that a variable or formal parameter has the same data type as a table column. Using anchored declarations ensures code clarity, reliability, and maintainability.

The magic of anchored attributes is simple: If the table or column to which the variable or constant is anchored changes, the variable’s program unit is invalidated. The program unit will then be automatically recompiled when it is next used. At the time of recompilation, the newest definition of the table or column will be used.

In addition, when you use anchored declarations, your code tells a clearer story to anyone who comes along later to maintain your code, because you are stating explicitly what kind of value the variable or constant is intended to hold.

Answer A is incorrect. This choice displays “Ginkgo” but then fails with ORA-06502: PL/SQL: numeric or value error: character string buffer too small. Because plch_show_tree1 hardcodes the maximum length to 6, the procedure cannot adjust.

Answer B is correct. I anchor the l_tree_name variable to the tree_name column of plch_trees so that when the column size changes, plch_show_tree2 is invalidated and then recompiled before its second execution. With that recompilation, the maximum length of l_tree_name changes to 10 and there is no problem getting the desired output.

Answer C is also correct but is not recommended. I anchor the l_tree record to the plch_trees table so that when the column size changes, plch_show_tree3 is invalidated and then recompiled before its second execution. With that recompilation, the maximum length of l_tree.tree_name changes to 10 and there is no problem. This method is not, however, recommended, because there is no reason to anchor to the entire record if you are working with only one column.

Question 2: Cursor FOR LOOP

I create and populate the following table:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO plch_parts VALUES (1, 'Mouse');
   INSERT INTO plch_parts VALUES (100, 'Keyboard');
   INSERT INTO plch_parts VALUES (500, 'Monitor');
   COMMIT;
END;
/

Which of the following choices display the names of all three parts in the table? Here’s answer A.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
   rec   plch_parts_cur%ROWTYPE;
BEGIN
   OPEN plch_parts_cur;
   LOOP
      FETCH plch_parts_cur INTO rec;
      EXIT WHEN plch_parts_cur%NOTFOUND;
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
   CLOSE plch_parts_cur;
END;
/

This is answer B.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
BEGIN
   FOR rec IN plch_parts_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
END;
/

Here’s answer C.

DECLARE
   CURSOR plch_parts_cur
   IS
      SELECT * FROM plch_parts;
   TYPE plch_parts_t IS TABLE OF plch_parts_cur%ROWTYPE;
   l_parts   plch_parts_t;
BEGIN
   SELECT *
     BULK COLLECT INTO l_parts
     FROM plch_parts;
   FOR indx IN 1 .. l_parts.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_parts (indx).partname);
   END LOOP;
END;
/

And here’s answer D.

BEGIN
   FOR rec IN (SELECT * FROM plch_parts)
   LOOP
      DBMS_OUTPUT.put_line (rec.partname);
   END LOOP;
END;
/

The cursor FOR LOOP is just about my favorite feature of PL/SQL. It’s a great demonstration of how thoroughly Oracle adapted Ada (the language on which PL/SQL was based) to the specialized world of database programming.

There’s no need to open, fetch, and close: Merely tell the PL/SQL engine you want to work with each row returned by the query. Plus, with your optimization level set to 2 (the default) or higher, this code is automatically optimized to return 100 rows with each fetch (resulting in performance similar to an explicit BULK COLLECT fetch).

You can embed the SELECT statement within the loop header, or you can declare an explicit cursor and reference it by name in the loop header. That means you can, again, parameterize the cursor and reuse it in multiple loops.

Keep the following in mind with cursor FOR LOOP:

• Never use a cursor FOR LOOP to fetch a single row.
• It’s a lazy way to avoid declaring the INTO variable or record, and that’s bad laziness.
• If you need to iterate through rows of data but then conditionally exit the loop under certain data conditions, use a WHILE or a simple loop with an explicit cursor. Why? Because every kind of FOR LOOP is saying implicitly, “I am going to execute the loop body for all iterations defined by the loop header” (N through M or SELECT). Conditional exits mean the loop could terminate in multiple ways, resulting in code that is hard to read and maintain.

Answer A is correct but is not recommended. If the only thing you need to do is iterate through all rows of a cursor and display data (a read-only loop), a cursor FOR LOOP is simpler and easier than the explicit cursor processing. There’s no need to declare a record, open the cursor, fetch the next row, check to see if it is time to stop, and close the cursor afterwards. Let the cursor FOR LOOP do the work.

Answer B is correct and is highly recommended. This is a nice, simple cursor FOR LOOP for read-only processing of data in the cursor. It is definitely the best way to go, unless there is data manipulation language (INSERT, UPDATE, DELETE) inside the loop. If that’s the case, you may want to use BULK COLLECT and FORALL.

Answer C is correct, but there’s overkill coding there. This code works, but why go to all that trouble? Don’t do this because BULK COLLECT is said to be faster, because that’s not correct in this case. With automatic optimization of PL/SQL code, a cursor FOR LOOP runs at a similar speed to BULK COLLECT. As long as you do not have data manipulation language inside the loop, use the cursor FOR LOOP instead.

Answer D is correct and is recommended. This is a good, simple application of the cursor FOR LOOP. The only difference between this choice and answer A is that here, I embed the SELECT statement directly inside the cursor FOR LOOP. The code is also more likely to be reused this way, especially if you put the cursor in a package specification.

Question 3: Make or no make

I create and populate the following table:

CREATE TABLE plch_employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER
)
/
BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Jobs', 1000000);
   INSERT INTO plch_employees
        VALUES (200, 'Ellison', 1000000);
   INSERT INTO plch_employees
        VALUES (300, 'Gates', 1000000);
   COMMIT;
END;
/

I then create the following two procedures. The first, plch_show_status, shows the status of a stored program unit. The second retrieves the last_name value of an employee. Both compile successfully.

CREATE OR REPLACE PROCEDURE plch_show_status (NAME_IN IN VARCHAR2)
IS
   l_validity   user_objects.status%TYPE;
BEGIN
   SELECT status
     INTO l_validity
     FROM user_objects
    WHERE object_name = NAME_IN;
   DBMS_OUTPUT.put_line ('Status of ' || NAME_IN || ': ' || 
l_validity);
END plch_show_status;
/

CREATE OR REPLACE PROCEDURE use_employees
IS
   l_name   plch_employees.last_name%TYPE;
BEGIN
   SELECT e.last_name
     INTO l_name
     FROM plch_employees e
    WHERE e.employee_id = 100;
END use_employees;
/

I then run the following statements to show the status of USE_EMPLOYEES three times:

BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/
ALTER TABLE plch_employees ADD first_name VARCHAR2(2000)
/
BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/
ALTER TABLE plch_employees MODIFY last_name VARCHAR2(2000)
/
BEGIN
   plch_show_status ('USE_EMPLOYEES');
END;
/

Which of the following choices shows the status that will be displayed on the screen?

Here’s answer A.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: INVALID

This is answer B.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID

Here’s answer C.

Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID
Status of USE_EMPLOYEES: INVALID

And here’s answer D.

Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID
Status of USE_EMPLOYEES: VALID

In software development, make (short for makefile) is a tool or process that automates the construction of executable programs from source code. In the world of Oracle Database programming with PL/SQL, make is entirely unnecessary.

That’s because Oracle Database automatically keeps track of dependencies between database objects (tables, views, types, packages, procedures, and so on) based on static references to objects within PL/SQL source code. The ALL_DEPENDENCIES view provides users access to this dependency information.

This automatic dependency tracking leads to three wonderful and magical aspects of PL/SQL:

• Oracle Database will automatically invalidate program units if any of the objects on which they depend are changed.
• Oracle Database will usually be able to silently recompile invalid program units, resolving all references to database objects according to the current state.
• Developers do not have to explicitly link program units or run any kind of make utility. Oracle Database takes care of all the details; program units are loaded for execution on demand.

Answer A is correct. The procedure starts off VALID because it compiled successfully, and it remains valid after a new column is added, and fine-grained dependency management leaves it VALID. When the column that use_employees references is modified, however, the program unit’s status is set to INVALID.

Answer B is incorrect. The procedure starts off VALID, because it compiled successfully. After a new column is added, fine-grained dependency management leaves the status VALID when a column is added to a table. How can a program unit be dependent on a column that never existed? When the column that use_employees references is modified, the program unit’s status is set to INVALID.

Answer C is also incorrect. The first line should show VALID for the status because the program compiled successfully. The second line should show VALID because fine-grained dependency management leaves the status VALID when a column is added to a table. When the column that use_employees references is modified, the program unit’s status is set to INVALID.

Answer D is incorrect as well. The first line should show VALID for the status because the program compiled successfully. The second line should show VALID because fine-grained dependency management leaves the status VALID when a column is added to a table. When the column that use_employees references is modified, the program unit’s status is set to INVALID.

Conclusion

Those of us working with PL/SQL tend to take all this wondrous behind-the-scenes goodness for granted, and there’s no reason why we shouldn’t. However, it’s also good to think about all the ways that Oracle Database makes database programming so productive.

Dig deeper

• The %ROWTYPE attribute
• The cursor FOR LOOP statement
• The FORALL statement

Illustration: Wes Rowell