Take the Transaction Test
What do you know about transaction features in PL/SQL?
By Steven Feuerstein
January/February 2016
One of the most important functions of PL/SQL is to help developers construct and manage transactions in their code. Which means that it is also very important for PL/SQL developers to understand transaction-related features of the language.
Here are three questions drawn from PL/SQL Challenge to test your knowledge of transaction-related features in PL/SQL.
Question 1
After I execute these statements, which of the choices will mean that “Last Count =0” is displayed?
CREATE TABLE plch_stuff (
id INTEGER PRIMARY KEY,
nm VARCHAR2 (5) UNIQUE)
/
CREATE OR REPLACE PROCEDURE plch_insert (
insert_row_in IN BOOLEAN DEFAULT TRUE)
IS
BEGIN
IF insert_row_in
THEN
INSERT INTO plch_stuff VALUES (1, 'Hat');
END IF;
DBMS_OUTPUT.put_line ('Count='||SQL%ROWCOUNT);
END;
/
a.
BEGIN
UPDATE plch_stuff SET nm = 'Glove';
plch_insert;
DBMS_OUTPUT.put_line ('Last Count='||SQL%ROWCOUNT);
END;
/
b.
BEGIN
UPDATE plch_stuff SET nm = 'Glove';
INSERT INTO plch_stuff VALUES (1, 'Hat');
DBMS_OUTPUT.put_line ('Last Count='||SQL%ROWCOUNT);
END;
/
c.
BEGIN
UPDATE plch_stuff SET nm = 'Glove';
plch_insert (insert_row_in => FALSE);
DBMS_OUTPUT.put_line ('Last Count='||SQL%ROWCOUNT);
Quiz Summary
SQL%ROWCOUNT is a call-level setting; it returns the number of rows returned by the most recently executed SELECT statement or modified by a nonquery data manipulation language (DML) statement.
Question 2
What is displayed on the screen after I execute the following statements?
CREATE TABLE plch_stuff (
id INTEGER PRIMARY KEY,
nm VARCHAR2 (5) UNIQUE)
/
BEGIN
INSERT INTO plch_stuff VALUES (1, 'Hat');
COMMIT;
END;
/
BEGIN
DELETE FROM plch_stuff WHERE id = 0;
DBMS_OUTPUT.put_line (
'Rows deleted = ' || SQL%ROWCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No rows deleted');
END;
/
a.
Rows deleted = 0
b.
Rows deleted = 1
c.
No rows deleted
Quiz Summary
When you execute an UPDATE or a DELETE and no rows are found to modify or remove, NO_DATA_FOUND is not raised (as occurs with a SELECT -INTO). Instead, the statement executes successfully and SQL%ROWCOUNT is set to 0.
Question 3
What is displayed on the screen after I execute the following statements?
CREATE TABLE plch_stuff
(
id INTEGER PRIMARY KEY,
nm VARCHAR2 (5) UNIQUE
)
/
DECLARE
l_count PLS_INTEGER;
BEGIN
INSERT INTO plch_stuff
VALUES (1, 'Hat');
INSERT INTO plch_stuff
VALUES (1, 'Jacket');
EXCEPTION
WHEN OTHERS
THEN
SELECT COUNT (*) INTO l_count FROM plch_stuff;
DBMS_OUTPUT.put_line ('Rows = ' || l_count);
END;
/
a.
Rows =
b.
Rows = 0
c.
Rows = 1
Quiz Summary
With very few exceptions relating directly to transactions, such as “ORA-06519: active autonomous transaction detected and rolled back,” the raising of an exception will not reverse the results of any successfully completed but not yet committed nonquery DML statements, such as inserts. This fact is reflected by the state of the plch_stuff table in the exception section.
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.