I'm trying to add a new column to a table in Oracle, and I want to know if it already exists or not. I have tried something like this:
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'minhaTabela'
AND COLUMN_NAME = 'minhaColuna')
How can I tell if a column already exists in a table in Oracle?
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: link