Accessing an inner error code when running sql using execute immediate

Question

I'm running the PL/SQL block below from a script. As you can see, it adds a column, and should catch any exceptions - among them, the one that would get thrown in the column already existed.

Now if I run this and the column already exists, I get an error:

Error code: -1735

Error message: ORA-01735: invalid ALTER TABLE option

That is all well and good, but if I run the inner SQL instead though; that is the SQL that follows execute immediate on it's own, I get this message instead, which is more precise:

ORA-01430: column being added already exists in table

The first error has the error code -1735, and I am able to catch with the when-clause that is commented out in the code below; if it is not commented out, the result will instead be:

Some other error occurred

I am not able to catch the -1430 exception though, even though that seems to be the root cause of the exception.

So my question: Is there any way to access this "inner" exception in this case? (is that even a valid term in this case?) In other words, can this be modified so as to provide a more specific error message?

DECLARE
    column_exists exception;
    pragma exception_init (column_exists , -1430);

    general_error exception;
    pragma exception_init (general_error , -1735);
BEGIN
    execute immediate 'ALTER TABLE my_table 
        ADD (some_column VARCHAR2(10 CHAR));';
EXCEPTION 
    -- I expected / wanted this to catch my error in order
    -- to let me output a more specific message:
    WHEN column_exists THEN    
        DBMS_OUTPUT.PUT_LINE('The column or index already exists');

 -- Note: Commented out, but would otherwise catch the general error.
 -- (I tested it here just to confirm that I can catch exceptions this way)
 --   WHEN general_error THEN    
 --       DBMS_OUTPUT.PUT_LINE('Some other error occurred');

 -- General catch: Generates the first message quoted above:
    WHEN OTHERS THEN    
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
END;
/

remove ; within '..... VARCHAR2(10 CHAR));' and try again. — Barbaros Özhan
– Barbaros Özhan, Commented Oct 14, 2019 at 12:03
You can also check view SELECT * FROM USER_TAB_COLS WHERE table_name = 'MY_TABLE'; to see whether column has been added already. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 14, 2019 at 12:22

Littlefoot · Accepted Answer · 2019-10-14 12:04:19Z

This is what I get after modifying your code a little bit:

In line #9, there's a semi-colon which should be removed as EXECUTE IMMEDIATE doesn't allow it, here:

ADD (some_column VARCHAR2(10 CHAR));';
                                   ^
                                   |
                            remove it

Sample table:

SQL> CREATE TABLE test
  2  AS
  3     SELECT * FROM dept;

Table created.

Your code:

SQL> DECLARE
  2     column_exists  EXCEPTION;
  3     PRAGMA EXCEPTION_INIT (column_exists, -1430);
  4
  5     general_error  EXCEPTION;
  6     PRAGMA EXCEPTION_INIT (general_error, -1735);
  7  BEGIN
  8     EXECUTE IMMEDIATE 'ALTER TABLE test
  9          ADD (loc VARCHAR2(10 CHAR))';   --> remove ; here
 10  EXCEPTION
 11     -- I expected / wanted this to catch my error in order
 12     -- to let me output a more specific message:
 13     WHEN column_exists
 14     THEN
 15        DBMS_OUTPUT.PUT_LINE ('The column or index already exists');
 16     -- Note: Commented out, but would otherwise catch the general error.
 17     -- (I tested it here just to confirm that I can catch exceptions this way)
 18     --   WHEN general_error THEN
 19     --       DBMS_OUTPUT.PUT_LINE('Some other error occurred');
 20
 21     -- General catch: Generates the first message quoted above:
 22     WHEN OTHERS
 23     THEN
 24        DBMS_OUTPUT.PUT_LINE ('Error code: ' || SQLCODE);
 25        DBMS_OUTPUT.PUT_LINE ('Error message: ' || SQLERRM);
 26  END;
 27  /
The column or index already exists

PL/SQL procedure successfully completed.

SQL>

Now, I'm not sure whether it was the case with the superfluous semi-colon or not. Remove it and rephrase the question, if necessary.

Thanks, it appears this was in fact the mistake. I was not able to detect it, since running the SQL on it's own (including the semi-colon) worked fine. Thanks for your input!

Collectives™ on Stack Overflow

Accessing an inner error code when running sql using execute immediate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related