VF=>user  ID=>    Login Feedback FAQ Blog
1,064,669 quizzes played | 2,953 active players

Actions


  On Attributes of SQL (Implicit) Cursors: SQL%ROWCOUNT (Oracle PL/SQL)      Add to Favorites

Summary

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query. Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore: When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point. When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Details

Assumes Oracle Database 10g Release 2 or higher
Intermediate Quiz, IDs: 13461/1324680/821668

Reviewer(s): Darryl Hurley, Vitaliy Lyanchevskiy

The Question

I execute the following statements:

CREATE TABLE plch_flowers
(
   id   INTEGER PRIMARY KEY,
   nm   VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO plch_flowers
        VALUES (1, 'Orchid');

   INSERT INTO plch_flowers
        VALUES (2, 'Rose');

   COMMIT;
END;
/

Which of the choices result in "RC=1" being displayed on the screen after execution?

The Choices [↑]

Explanation of Result Icons
Choice 1 (26596)
DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id
     INTO l_id
     FROM plch_flowers
    WHERE nm = 'Orchid';

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

I select one row and SQL%ROWCOUNT shows me that.




87%
Choice 2 (26597)
DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id INTO l_id FROM plch_flowers;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.




22%
Choice 3 (26598)
BEGIN
   INSERT INTO plch_flowers
      SELECT id * 3, UPPER (nm) FROM plch_flowers;

   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

I insert two rows and SQL%ROWCOUNT shows me that.




91%
Choice 4 (26599)
DECLARE
   l_id   INTEGER;
BEGIN
   INSERT INTO plch_flowers
        VALUES (3, 'Tulip');

   SAVEPOINT inserted_row;

   INSERT INTO plch_flowers
        VALUES (3, 'Lotus');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK TO inserted_row;
      DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the savepoint. This choice displays "RC=0".




49%
Choice 5 (26600)
DECLARE
   l_id   INTEGER;

   PROCEDURE insert_and_save
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO plch_flowers
         SELECT id * 3, UPPER (nm) FROM plch_flowers;

      COMMIT;
   END;
BEGIN
   INSERT INTO plch_flowers
        VALUES (10, 'Ambrosia');

   insert_and_save;
   DBMS_OUTPUT.put_line ('RC=' || SQL%ROWCOUNT);
END;
/

When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.




40%

Answer [↑]

Static or embedded SQL are SQL statements that are written natively into your PL/SQL programs (as opposed to defining them as expressions for execution as dynamic SQL).


An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.

Oracle defines a number of attributes of implicit cursors, whose value can be obtained through the SQL%attribute syntax. SQL%attribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL%attribute is NULL.

An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs.

The most recently run SELECT or DML statement might be in a different scope (another subprogram call that has now completed, for example). To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.


SQL%ROWCOUNT returns NULL if no SELECT or DML statement has run. Otherwise, it returns the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER).

If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.

Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore:

  • When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point.
  • When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.

Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
77
50
247
160
60
Best
1
100
478
66
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation