SQL%ROWCOUNT [message #655917] |
Fri, 16 September 2016 04:32 |
|
anders_e
Messages: 2 Registered: September 2016
|
Junior Member |
|
|
Hi there, I am having trouble to understand SQL%ROWCOUNT.
I understand that only DML statements affect the value, but I have been unable to find the reason for this behaviour.
Note that rows actually were deleted from what in the example is called TABLE_3. But still it prints 0.
Can anyone explain this?
SQL SCRIPT:
DELETE FROM TABLE_2 A WHERE A.VALUE = (SELECT B.VALUE FROM TABLE_1 B WHERE A.VALUE = B.VALUE);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' ROWS DELETED FROM TABLE_2');
DELETE FROM TABLE_3 A WHERE A.VALUE = (SELECT B.VALUE FROM TABLE_1 B WHERE A.VALUE = B.VALUE);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' ROWS DELETED FROM TABLE_3');
LOGFILE:
27 ROWS DELETED FROM TABLE_2
0 ROWS DELETED FROM TABLE_3
|
|
|
|
|
Re: SQL%ROWCOUNT [message #655925 is a reply to message #655917] |
Fri, 16 September 2016 09:57 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
If your observation is correct (as you provided no evidence of it), the rows from TABLE_3 were most probably deleted in the first DELETE statement.
Maybe by the foreign key constraint TABLE_3.A_VALUE refencing TABLE_2.A_VALUE with ON DELETE clause set to CASCADE.
Or, by some already-forgotten ON DELETE row level trigger on TABLE_2 which is still "working" as coded before.
But, you could easily check whether they are still present in TABLE_3 before the second DELETE statement to confirm this.
|
|
|
Re: SQL%ROWCOUNT [message #655981 is a reply to message #655925] |
Mon, 19 September 2016 07:27 |
|
anders_e
Messages: 2 Registered: September 2016
|
Junior Member |
|
|
Thank you for your input. And thanks to flyboy for getting me in the right direction. I was going nuts trying to recreate this in a testcase which I failed to do.
I know I have had output before that confirmed the deletions I knew took place.
However, the first time this massive data deletion job ran, all indexes and constraints were disabled.
The following times, we are already down to the amount of data we would like to keep and it therefore runs as a batch job deleting small amounts of data every week, found out that although we have no delete rules usually there are actually a few constraints implemented with a cascade delete rule.
Cheers
/Anders
|
|
|