Catching an exception when changing a table in Oracle

Question

I am trying to write a command in Oracle that will dry an ADD or MODIFY column depending on whether 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 the ALTER command inside BEGIN. Is there a way to achieve this using a single SQL command in Oracle?

Thanks!

Answer 1

To put DDL in a PL / SQL block, you will need to use dynamic SQL.

Personally, I would check if the column exists first, and then produces a DDL. Sort of

 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 do not have access to DBA_TAB_COLS , you can also use ALL_TAB_COLS or USER_TAB_COLS , depending on which schema is in the table and what rights you have in the table.

Answer 2

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;