title: oracle execute immediate not executing
body: <p>I am new to Oracle (11gr2) and I have the following script:</p>
  
  <pre><code>BEGIN
      DECLARE 
          source varchar2(1);
  
      BEGIN
          dbms_output.enable;
  
  
          BEGIN
              EXECUTE IMMEDIATE 'DROP VIEW SP_AD;';
              SELECT SOURCE INTO source FROM map_switch WHERE ROWNUM = 1;
              IF source = 'A' 
                  THEN 
                      EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
                      EXECUTE IMMEDIATE 'RENAME TABLE SP_AD_A TO SP_AD;';
                  ELSE 
                      EXECUTE IMMEDIATE 'DROP TABLE SP_AD_A;';
                      EXECUTE IMMEDIATE 'RENAME TABLE SP_AD_B TO SP_AD;';
              END IF;
              COMMIT WORK;
                      dbms_output.put_line('SP_AD table issue fixed');
              EXCEPTION
                  WHEN OTHERS THEN
                      dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
                      ROLLBACK WORK;
          END;        
      END;
  END;
  /
  </code></pre>
  
  <p>Essentially, its determining which table to drop, then it drops the view and renames the other table.  </p>
  
  <p>If I run the statements individually, it works perfectly well, but in the script above, it returns procedure executed successfully but nothing was executed.</p>
  
  <p>I'm suspecting that its rolling back for some odd reason, but I'm hesitating to execute it without the rollback in place (these tables have in excess of 300,000 records).</p>
  
  <p>Can someone tell me what's wrong and also, is there something wrong with my exception block? </p>