CODEWITHSUNDEEP
sqloracle-databasealter-table
Paul
Paul

Reputation: 3954

Catching an exception while altering a table in Oracle

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!

Upvotes: 4

Views: 6595

Answers (2)

Paul
Paul

Reputation: 3954

I found a solution based on this post.

DECLARE v_column_exists number := 0;   
BEGIN   
  SELECT COUNT(*) INTO v_column_exists
  FROM ALL_TAB_COLUMNS 
  WHERE TABLE_NAME = 'MY_TABLE'
  AND COLUMN_NAME = 'COL_NAME';

  IF (v_column_exists = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE ADD ( COL_NAME VARCHAR2(200) )';   
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE MODIFY ( COL_NAME VARCHAR2(200) )';
  END IF;
END;

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 6

Related Questions