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

Question

How do I add a simple check before adding a column to a table for an oracle db  I ve included the SQL that I m using to add the column   ALTER TABLE db tablename    ADD columnname NVARCHAR2 30

User · Accepted Answer

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.

User · Answer

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 database SELECT        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 query SELECT        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

User · Answer

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

User · Answer

To check column exists  select column name as found from user tab cols where table name      TABLE NAME    and column name      COLUMN NAME      Reference link

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

Examples related to sql

Examples related to oracle

Examples related to plsql