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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With