Database, SQL and PL/SQL

Take the Transaction Test

What do you know about transaction features in PL/SQL?

By Steven Feuerstein Oracle ACE Director

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

 TEST your SQL and PL/SQL knowledge.

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

 MEET the Oracle Developer Advocates team.

 

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.