OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT (SQL*Plus: Release 12.1.0.1.0, Linux x86 64-bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: SQL%ROWCOUNT [message #655920 is a reply to message #655917]

Fri, 16 September 2016 05:49

Littlefoot
Messages: 21809
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).

Report message to a moderator

Re: SQL%ROWCOUNT [message #655923 is a reply to message #655917]

Fri, 16 September 2016 09:54

Michel Cadot
Messages: 68653
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.

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Dynamic SQL : ORA-01006: bind variable does not exist (merged)
Next Topic:	DECLARE a FUNCTION

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 14:17:51 CDT 2024