oracle execute immediate not executing

Question

I am new to Oracle (11gr2) and I have the following script:

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;
/

Essentially, its determining which table to drop, then it drops the view and renames the other table.

If I run the statements individually, it works perfectly well, but in the script above, it returns procedure executed successfully but nothing was executed.

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).

Can someone tell me what's wrong and also, is there something wrong with my exception block?

Answer 1

As pointed out by commenters, there are a few reasons why your code isn't working as expected.

Firstly, don't use semicolons inside the strings that you pass to EXECUTE IMMEDIATE, as doing that will give you an ORA-00911 'invalid character' error:

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2

After running this, you can then verify that the table still exists:

SQL> SELECT * FROM SP_AD_B;

no rows selected

(I don't have your table SP_AD_B, so I just created one named SP_AD_B with a single integer column in it. I didn't bother putting any data in it.)

If you remove the semicolon inside the string, not the one outside, it works:

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SP_AD_B;
SELECT * FROM SP_AD_B
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Now that the table's gone, we get an error attempting to query it.

Hopefully, this should allow you to fix your script so that it works and drops the relevant tables.

But why weren't you getting any helpful information in your output message? Well, let's recreate the SP_AD_B table, and reintroduce the semicolon, and try dropping the table again, but with an EXCEPTION handler similar to yours:

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
  6  END;
  7  /
Exception, rolling back transaction, SP_AD not resolved.

PL/SQL procedure successfully completed.

In this case, we got an error message telling us something went wrong, so the table wasn't dropped. But what went wrong? There are thousands of errors that Oracle can report, and it can be difficult to guess what the problem is without knowing the error message.

There are a number of approaches you can take here. Firstly, you could write the error message, in SQLERRM to dbms_output:

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
  6      dbms_output.put_line('Error message was: ' || SQLERRM);
  7  END;
  8  /
Exception, rolling back transaction, SP_AD not resolved.
Error message was: ORA-00911: invalid character

PL/SQL procedure successfully completed.

You can also use dbms_utility.format_error_backtrace to return the current stacktrace as a string, if you so wish. That might help you figure out where the error came from.

Alternatively, you can reraise the exception. Using RAISE on its own in an EXCEPTION handler reraises the current exception:

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5      dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
  6      RAISE;
  7  END;
  8  /
Exception, rolling back transaction, SP_AD not resolved.
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 6

However, given the fact that your EXCEPTION handler isn't really doing anything helpful, the best approach is quite probably to get rid of it altogether.

Your exception handler doesn't achieve anything because you can't commit or rollback DDL statements such as CREATE, ALTER, DROP or TRUNCATE. Each of these statements issues a COMMIT immediately before and after it runs. If a DROP succeeds but a RENAME fails, you can't get the dropped table back by rolling back a transaction. I'd recommend getting rid of your COMMIT WORK and ROLLBACK WORK statements.

Finally, commenter Jeffrey Kemp noticed this line:

SELECT SOURCE INTO source FROM map_switch WHERE ROWNUM = 1;

This assigns into a variable named source the value of the column SOURCE from some arbitrary row of the table map_switch. It could be any row; as you haven't specified any ordering, Oracle is free to order the rows of map_switch however it likes.

If there's only one row in the table, then it's clear which row you'll get back. However, if this is the case, why specify ROWNUM = 1? Does the table have more than one row and is the ROWNUM = 1 part is just there to silence an 'exact fetch returns more than requested number of rows' error?

You would be better off doing something like the following:

SELECT SOURCE INTO source
  FROM (SELECT SOURCE FROM map_switch ORDER BY some_column)
 WHERE ROWNUM = 1;

I don't know what columns there are in your map_switch table, so I've just used some_column above as a placeholder for one of them. Choose a column that has unique values, if possible.

Note that we can't simply do SELECT ... WHERE ROWNUM = 1 ORDER BY some_column as that would apply the ROWNUM = 1 clause before doing the sorting, and there's not a lot of point sorting a single row as there's only one order it can be returned in.