Catching an exception while altering a table in Oracle

Question

I'm trying to write a command in Oracle that will wither ADD or MODIFY a column depending on whether or not it already exists. Basically something like:

BEGIN

  ALTER TABLE MY_TABLE ADD ( COL_NAME VARCHAR2(100 );

  EXCEPTION WHEN OTHERS THEN
    ALTER TABLE MY_TABLE MODIFY ( COL_NAME VARCHAR2(100) );

END;

However, Oracle complains about having the ALTER command inside of BEGIN. Is there a way to achieve this using a single SQL command in Oracle?

Thanks!

Answer 1

In order to put DDL in a PL/SQL block, you would need to use dynamic SQL.

Personally, I'd check whether the column exists first and then issue the DDL. Something like

DECLARE
  l_cnt INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM dba_tab_cols
   WHERE table_name  = 'MY_TABLE'
     AND owner       = <<owner of table>>
     AND column_name = 'COL_NAME';

  IF( l_cnt = 0 )
  THEN
    EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD( col_name VARCHAR2(100) )';
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY( col_name VARCHAR2(100) )';
  END IF;
END;

If you don't have access to DBA_TAB_COLS, you could also use ALL_TAB_COLS or USER_TAB_COLS depending on what schema the table resides in and what privileges you have on the table.