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 · 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

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

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

[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