Subscribe

Share

Database, SQL and PL/SQL

PL/SQL Surprises

Quiz yourself on exceptions, rollbacks, and collections.

By Steven Feuerstein Oracle ACE Director

March/April 2016

PL/SQL is a powerful and, in many ways, very straightforward database programming language. There’s nothing fancy—it’s exactly what you need and nothing you don’t.

Given that dedication to necessity, it may come as a surprise that some of the ways PL/SQL behaves come as a surprise to developers, especially those new to PL/SQL. This PL/SQL column puts a spotlight on a few of the common sources of PL/SQL surprises, by quizzing you and explaining the behaviors.

Exceptions and Rollbacks

PL/SQL was designed to be a powerful, simple language for executing SQL statements securely and with optimal performance. So PL/SQL code often contains many nonquery data manipulation language (DML) statements (inserts, updates, and deletes) as part of business transactions. Developers are often unclear on how errors raised by SQL statements in a PL/SQL program affect the overall transaction.

I execute the following statement:

CREATE TABLE plch_plants
(
   plant_id     INTEGER PRIMARY KEY,
   plant_name   VARCHAR2 (4) 
)
/

Question 1
Exceptions and Rollbacks

Which of the following choices displays “Count=1” after execution?

a.

DECLARE
   l_count INTEGER;
BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Rose');
   BEGIN
      INSERT INTO plch_plants
           VALUES (2, 'Kudzu');
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
   END;
   SELECT COUNT (*) INTO l_count FROM plch_plants;
   DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

b.

DECLARE
   l_count INTEGER;
BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Rose');
   INSERT INTO plch_plants
        VALUES (2, 'Kudzu');
EXCEPTION
   WHEN OTHERS
   THEN
      SELECT COUNT (*) INTO l_count FROM plch_plants;
      DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

c.

DECLARE
   l_count INTEGER;
BEGIN
   INSERT INTO plch_plants
        VALUES (1, 'Kudzu');
   INSERT INTO plch_plants
        VALUES (2, 'Rose');
EXCEPTION
   WHEN OTHERS
   THEN
      SELECT COUNT (*) INTO l_count FROM plch_plants;
      DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/


Quiz Summary

With very few exceptions (no pun intended), the raising of an exception will not reverse the results of any successfully completed but not yet committed DML statements.

Read more about transaction processing and control.

 

Question 2
What an Exception Section Handles

The block structure of PL/SQL is both a fundamental element of the language and a powerful feature for writing maintainable applications. Each block is composed of as many as three sections: declaration, execution, and exception. The interaction between these sections is not always clear.

Which of the following choices displays “Here comes spring!” after execution?

a.

BEGIN
   RAISE VALUE_ERROR;
   DBMS_OUTPUT.put_line ('Here comes spring!');
END;
/

b.

DECLARE
   l_number   NUMBER (3, 0) := 2016;
BEGIN
   DBMS_OUTPUT.put_line ('Here comes spring!');
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Here comes spring!');
END;
/

c.

DECLARE
   l_number NUMBER (3, 0);
BEGIN
   l_number := 2016;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Here comes spring!');
END;
/


Quiz Summary

The exception section of a PL/SQL block can trap only an exception raised in the executable section of that block. If the exception is raised in the declaration section—in an attempt to assign a default value to a variable or a constant—the exception always propagates out of that block unhandled.

Read more about exception propagation.

 

Question 3
Surprising Variations Within Collections

Collections are PL/SQL’s arraylike structures, and there are three—count ’em, three!—different types of collections, each with their own characteristics and use cases.

I execute these statements:

CREATE TYPE plch_numbers_nt IS TABLE OF NUMBER
/
CREATE TYPE plch_numbers_va IS VARRAY (3) OF NUMBER
/

Which of the following choices displays “Count=0” after execution?

a.

DECLARE
   TYPE plch_numbers_aa IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;
   l_numbers   plch_numbers_aa;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/

b.

DECLARE
   l_numbers   plch_numbers_nt;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/

c.

DECLARE
   l_numbers   plch_numbers_va;
BEGIN
   l_numbers.delete;
   DBMS_OUTPUT.put_line ('Count=' || l_numbers.COUNT);
END;
/


Quiz Summary

When working with nested tables and varrays, you must—under most circumstances—first initialize those collections by calling their constructor function, which has the same name as the collection type. This is not necessary with associative arrays.

Read more about PL/SQL collections and records.


Next Steps

 TEST your SQL and PL/SQL knowledge.

 WATCH Feuerstein’s “Practically Perfect PL/SQL” videos.

 MEET the Oracle Developer Advocates team.

 

Photography by Ricardo Gomez, Unsplash