VF=>user  ID=>    Login Feedback FAQ Blog
1,050,815 quizzes played | 1,461 active players

Actions

Important News!

We've built a new "skin" for our quiz platform: the Oracle Dev Gym. We'd love to hear what you think of it. Click on the Dev Gym image on the right of your home page to check it out.



  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