26 October 2010

Questions about SQL%ROWCOUNT and FORALL from 20 October Quiz (1523)

Whoops! I overlooked (or, to be more honest, was too busy to respond to) a couple of submissions by players regarding this quiz that tested your knowledge of using the INDICES OF clause with FORALL. Here are the questions/comments: * "When the update statement is not executed, sql%rowcount is not 0, but it is NULL. dbms_output does not write out 0, but a blank space." * "Can you please give more information on why SQL%ROWCOUNT is null returned as null when we use the forall construct with no elements referenced? I am wondering if there will ever a case where a null is returned because the update is never fired." In fact, it is true: if no SQL statement is executed, then SQL%ROWCOUNT returns NULL, as I verified by running the following block (see the Verification Code for this quiz on the Past Quizzes page for the create table statement for parts, and so on).
DECLARE
  TYPE part_aat IS TABLE OF parts.partnum%TYPE
                      INDEX BY PLS_INTEGER;

  l_parts   part_aat;
BEGIN
  FORALL l_index IN 1 .. 0
     UPDATE parts
        SET partname = UPPER (partname)
      WHERE partnum = l_parts (l_index);

  DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);
END;
Was I, then, wrong in scoring the following choice as correct (with the associated explanation)?
BEGIN
  update_parts (100000, 10000000);
END;
"And the output displayed is: Rows modified = 0. There are no elements defined in l_part_indices between 100000 and 1000000. As a result, none of the index values in l_parts will be used, no elements of the l_parts array will be bound into the DML statement, and no rows will be updated."
Hmmm. A very good question. SQL%ROWCOUNT definitely returns 1, but I cannot explain it to my satisfaction. It looks as if, with the INDICES OF and BETWEEN clauses, the FORALL gets "far enough" to be considered by the SQL engine to have run something, but having not actually changed any rows returns 0 for that attribute. Anyone out there have a better explanation? Cheers, SF

2 comments:

  1. Using the forall with indices of is just like using a for loop.
    In the case of update_parts (100000, 10000000);
    it shows 'Rows modified=0'. It's the same as if you ran this:
    BEGIN
    FOR indx IN 1 .. 2
    LOOP
    UPDATE parts
    SET partname = UPPER(partname)
    WHERE partnum = 40000;
    END LOOP;
    DBMS_OUTPUT.put_line('row count is ' || SQL%ROWCOUNT);
    END;

    Since it opens the for loop, it attemps to execute the update statement, but no rows are updated, so SQL%ROWCOUNT = 0.

    In the case of update_parts (-1000,-2000); it shows 'Rows modified='. SQL%ROWCOUNT is null just like it is for this:
    BEGIN
    FOR indx IN 2 .. 1
    LOOP
    UPDATE parts
    SET partname = UPPER(partname)
    WHERE partnum = 40000;
    END LOOP;
    DBMS_OUTPUT.put_line('row count is ' || SQL%ROWCOUNT);
    END;
    SQL%ROWCOUNT is null because the for loop will never execute sincer the upper bound is less than the lower bound. In the case of update_parts(-1000,-2000), the upper bound is less than the lower bound. Thus the for loop is never executed and it doesn't attempt to execute the update statement. Therefor SQL%ROWCOUNT is null.

    Does this answer the question or did I not undertand the question?

    Jennifer Schiltz

    ReplyDelete
  2. Hi Steven,

    I don't know whether I should comment, because I think Jen's comment already says it all. At least regarding player feedback.
    However, there seems to be one more issue, introduced by you in your original post: "Hmmm. A very good question. SQL%ROWCOUNT definitely returns 1 ..."
    I cannot reproduce this behaviour.

    Regards,
    Piet

    ReplyDelete