Alter Table if column doesn't exist

Question

In a table, I want to check if a particular column exists or not. If the column does not exist, I want to alter the table and create that column.

I am using Oracle 11g.

Answer 1

Try this:

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS 
where OWNER = '<SCHEMA_NAME>' 
and TABLE_NAME = '<TABLE_NAME>' 
and COLUMN_NAME = '<COLUMN_NAME>';

IF p_count = 0 THEN
    --add your column
END IF;

Eventually (depending on the rights) You can use user_tab_columns.