No, "on delete cascade" doesn't solve all of the problems, but it solves one very difficult problem. I assume it's difficult because it's an area where aD hasn't done a good job solving it in the past:
You build package A. Later on, someone builds package B which references rows in tables created by A.
The author of package A has no way to drop the bits of package B which refer to package A's tables. Using "on delete cascade/set null" in package B solves this problem. The author of package B could also use triggers to do this (which, after all, is how "on delete" is implemented) or modify package A (which hard-codes a dependency on B in A, which sucks big time). This last approach was the traditional ACS approach in 3.x and folks and people forever forgot to make the necessary changes to package A. This is why the "nuke user" script has hardly ever worked.
In other words, the author of package A has to take responsibility for cleaning up any garbage it introduces into the system, most certainly. But package B also needs to clean itself up if package A goes away. "on delete" is a easy and clean way to do so, one that's not nearly as prone to failure due to forgetfulness as was done in 3.x.
When Philip et al first began working with Oracle, I don't think "on delete" was implemented, it's fairly recent AFAIK.
