How to check if a column exists before adding it to an existing table in PL/SQL?

All the metadata about the columns in Oracle Database is accessible using one of the following views.

user_tab_cols; -- For all tables owned by the user

all_tab_cols ; -- For all tables accessible to the user

dba_tab_cols; -- For all tables in the Database.

So, if you are looking for a column like ADD_TMS in SCOTT.EMP Table and add the column only if it does not exist, the PL/SQL Code would be along these lines..

DECLARE  v_column_exists number := 0;  BEGIN  Select count(*) into v_column_exists    from user_tab_cols    where upper(column_name) = 'ADD_TMS'      and upper(table_name) = 'EMP';      --and owner = 'SCOTT --*might be required if you are using all/dba views  if (v_column_exists = 0) then      execute immediate 'alter table emp add (ADD_TMS date)';  end if;end;/

If you are planning to run this as a script (not part of a procedure), the easiest way would be to include the alter command in the script and see the errors at the end of the script, assuming you have no Begin-End for the script..

If you have file1.sql

alter table t1 add col1 date;alter table t1 add col2 date;alter table t1 add col3 date;

And col2 is present,when the script is run, the other two columns would be added to the table and the log would show the error saying "col2" already exists, so you should be ok.

Or, you can ignore the error:

declare    column_exists exception;    pragma exception_init (column_exists , -01430);begin    execute immediate 'ALTER TABLE db.tablename ADD columnname NVARCHAR2(30)';    exception when column_exists then null;end;/

Normally, I'd suggest trying the ANSI-92 standard meta tables for something like this but I see now that Oracle doesn't support it.

-- this works against most any other databaseSELECT    * FROM     INFORMATION_SCHEMA.COLUMNS C     INNER JOIN         INFORMATION_SCHEMA.TABLES T         ON T.TABLE_NAME = C.TABLE_NAME WHERE     C.COLUMN_NAME = 'columnname'    AND T.TABLE_NAME = 'tablename'

Instead, it looks like you need to do something like

-- Oracle specific table/column querySELECT    * FROM    ALL_TAB_COLUMNS WHERE    TABLE_NAME = 'tablename'    AND COLUMN_NAME = 'columnname'

I do apologize in that I don't have an Oracle instance to verify the above. If it does not work, please let me know and I will delete this post.