PL/SQL Surprises
Quiz yourself on exceptions, rollbacks, and collections.
By Steven Feuerstein
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 RollbacksPL/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
|
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.