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
Using the forall with indices of is just like using a for loop.
ReplyDeleteIn 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
Hi Steven,
ReplyDeleteI 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