How do I know if a column exists in an Oracle table?

Question

I’m trying to add a new column to an Oracle table, and I want to know if it already exists or not. I’ve tried something like this:

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'minhaTabela' 
            AND  COLUMN_NAME = 'minhaColuna')

How can I know if a column already exists in an Oracle table?

Answer 1

 Uso essa query:

 SELECT column_name AS FOUND
 FROM user_tab_cols WHERE table_name = '__TABLE_NAME__'
 and column_name = '__COLUMN_NAME__';

A query vai retorna  a coluna se ela existir;

Now if you want to create if it DOES NOT exist, the path is through PL/SQL:

  /*Verifica se coluna já existe e se não houver insere com valor padrão 'A'*/
DECLARE 
 col_count  integer;
BEGIN 
 SELECT count(*)
   into col_count
 FROM user_tab_columns
 WHERE table_name = '<nomeTabela>'
 AND column_name = '<nomeColuna>';

 IF col_count = 0 THEN 
    EXECUTE IMMEDIATE 'ALTER TABLE nomeTabela add nomeColuna char(1) default ''A'' not null';
    COMMIT;
 END IF;
 exception when others then
 if sqlcode = -01430 || -06512 then
   null;
 end if;
END;

PL/SQL removed from this link: http://respostas.guj.com.br/38479-inserindo-coluna-na-tabela-oracle-com-condicional