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!
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.
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;
