Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT (SQL*Plus: Release 12.1.0.1.0, Linux x86 64-bit)
SQL%ROWCOUNT [message #655917] Fri, 16 September 2016 04:32 Go to next message
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 #655920 is a reply to message #655917] Fri, 16 September 2016 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 21440
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind posting copy/paste of your SQL*Plus session which contains that code? Please, properly format it (there are instructions at the top of the messages list in this forum).
Re: SQL%ROWCOUNT [message #655923 is a reply to message #655917] Fri, 16 September 2016 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
But still it prints 0.
So no rows were deleted.

Re: SQL%ROWCOUNT [message #655925 is a reply to message #655917] Fri, 16 September 2016 09:57 Go to previous messageGo to next message
flyboy
Messages: 1856
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 Go to previous message
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
Previous Topic: Dynamic SQL : ORA-01006: bind variable does not exist (merged)
Next Topic: DECLARE a FUNCTION
Goto Forum:
  


Current Time: Tue Oct 23 13:59:47 CDT 2018