Whilst trying to improve the maintainability of an SP in one of our systems I decided that using a loop would be better than having an array of values (table names in this case) hard coded in and tried to re factor the code accordingly so that adding or removing a table to the system didn't require editing of the array. Leaving aside the whys and wherefores of loops for the moment (I know the arguments against them very well), can anyone explain what's happening?
Imagine two users, SourceUser and DestUser both in the same database, each with their tables in the same tablespace. A bunch of stored procedures in SourceUser populate data from SourceUser into DestUser for reporting purposes. As part of this, the first procedure to be run drops all the tables in DestUser and re-creates them. Again, not debating the relative merits of doing that here.
SourceUser has Drop Any Table and Create Any Table privileges on DestUser. There is one table in DestUser that we want to keep. So, the SQL I constructed in the procedure looks like this:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE TABLE_NAME != 'MIDBLOG' AND OWNER = sTarget_DB) Loop
Begin
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;
In this case sTarget_DB is set to DestUser, and this code is being run against SourceUser.
When the procedure is run I find that no tables have been deleted (I confirmed that there were a couple of dozen tables including one named MIDBLOG before starting). I ran it in SQL Developer debug mode and the execution never even gets to the inside of the loop as it appears to think it has no rows to process but I know for certain that the select statement would return a couple of dozen table names.
Next I amended it to this:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE OWNER = sTarget_DB) Loop
Begin
If T.TABLE_NAME != 'MIDBLOG' THEN
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
End If;
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;
After running this the only table that WAS removed was the very one I didn't want removed! Even more odd was that the loop only executed once, as though the select query was only returning one row. I could see it happening if I ran the procedure in debug on SQL Developer 3.2. We did the same thing on a colleagues PC on SQL Developer (possibly 3.1) and again the loop only executed once but this time it correctly decided not to drop the table MIDBLOG and again left everything else alone.
If I run either of the above examples as an anonymous block in SQL Developer it does exactly what I expect it to do. I tried the more verbose explicit cursor declaration and had the same results as before. At no time did I get any exceptions.
All this was on Oracle 10g Enterprise Edition Release 10.2.0.4.0 (64bit). As soon as tried it on Oracle 11g Enterprise Edition Release 11.2.0.1.0 (64bit) it worked just fine. Why on earth should such a basic requirement exhibit such wildly different behaviour across the two versions? Can it work as I want in both versions with the same bit of code?
othersexception and its accompanying comment - there are several reasons why adrop tablecommand command could fail other than the table not existing. It would be better to handle the -942 exception explicitly and let the others fail properly.