Databases
StackOverflow2013
Postsdbo
POOracle For Loop in Stored Procedure Not Looping
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle For Loop in Stored Procedure Not Looping
    primarykey
    data
    text
    <p>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?</p> <p>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.</p> <p>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:</p> <pre><code>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; </code></pre> <p>In this case sTarget_DB is set to DestUser, and this code is being run against SourceUser.</p> <p>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.</p> <p>Next I amended it to this:</p> <pre><code>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; </code></pre> <p>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.</p> <p>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. </p> <p>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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSteve Pettifer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload