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

by

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.

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. PL SQL UPDATE STATEMENT
  3. Splitting string into multiple rows in Oracle
  4. Search All Fields In All Tables For A Specific Value (Oracle)
  5. Is there a combination of “LIKE” and “IN” in SQL?
  6. What is the string concatenation operator in Oracle?
  7. Is it possible to output a SELECT statement from a PL/SQL block?
  8. REGEX to select nth value from a list, allowing for nulls
  9. SQL to generate a list of numbers from 1 to 100
  10. How to use BOOLEAN type in SELECT statement
  11. create table with sequence.nextval in oracle [duplicate]
  12. IF EXISTS condition not working with PLSQL
  13. passing table and column name dynamically using bind variables
  14. WHERE IN condition not accepting String value
  15. Is there any way to flush output from PL/SQL in Oracle?
  16. Sleep function in ORACLE
  17. Solution to “cannot perform a DML operation inside a query”?
  18. PLSQL Insert into with subquery and returning clause
  19. What does the colon sign “:” do in a SQL query?
  20. How to query a CLOB column in Oracle
  21. Oracle (Old?) Joins – A tool/script for conversion?
  22. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  23. Advice Using Pivot Table in Oracle
  24. Creating or simulating two dimensional arrays in PL/SQL
  25. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  26. How to use Alias in Where clause? [duplicate]
  27. Subtracting Dates in Oracle – Number or Interval Datatype?
  28. How to call Oracle MD5 hash function?
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment