Skip to main content

SQL%ROWCOUNT: What/how much did my SQL statement do?

This post is courtesy of the PL/SQL Challenge quiz ending 27 November 2015:

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.

Here's the code for the quiz - see how you do!

And of course sign up to take each weekly quiz as it is released (you can even compete for international rankings).

I execute the following statements (which you can easily run yourself on LiveSQL):


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?

DECLARE
   l_id   INTEGER;
BEGIN
   SELECT id
     INTO l_id
     FROM plch_flowers
    WHERE nm = 'Orchid';

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

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;
/

BEGIN
   INSERT INTO plch_flowers
      SELECT id * 3, UPPER (nm) FROM plch_flowers;

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

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;
/

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;
/


Background on SQL%ROWCOUNT

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.

Comments

  1. Thanks a lot for the DBMS_lob.substr. I never would have came up with that.

    ReplyDelete

Post a Comment

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...