Inserting column in Oracle table with conditional

Question

I need to create a column in an existing table but need to do with a checking condition if this column already exists in the bank I did so but it throws an error:

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';
 END IF;
END;  

The log of the error:

Erro a partir da linha : 4 no comando -
DECLARE 
col_count  integer;
BEGIN 
SELECT count(*)
into col_count
FROM user_tab_columns
WHERE table_name = 'populis.painel'
AND column_name = 'atrAtivoInativo';

IF col_count = 0 
THEN 
 EXECUTE IMMEDIATE 'ALTER TABLE POPULIS.PAINEL add atrAtivoInativo char(1) default 'A' not null';
END IF;
END;
Relatório de erros -
ORA-06550: linha 12, coluna 88:
PLS-00103: Encontrado o símbolo "A" quando um dos seguintes símbolos era esperado:

* & = - + ; < / > at in é mod lembrete not rem retornar
retornando <um expoente (**)> <> ou != ou ~= >= <= <> e ou
como like2 like4 likec entre into usando || multiset bulk
membro submultiset
O símbolo "* foi inserido antes de "A" para continuar.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

Fala Guilherme!

The problem is on the line:

EXECUTE IMMEDIATE 'ALTER TABLE <nomeTabela> add <nomeColuna> char(1) default 'A' not null'

As indicated by the error:

ORA-06550: row 12, column 88

It turns out that the String that represents your command to change the table is broken. The command should be entirely involved by apostrophes, but note that when you involve the A with apostrofe you broke the String in two getting the command 'ALTER TABLE add char(1) default' and the 'not null' with an A letter in the middle.

I confess that I do not know what this means default 'A' not null but if that’s right then use escape characters to represent these apostrophes involving the A, in case would be:

EXECUTE IMMEDIATE 'ALTER TABLE <nomeTabela> add <nomeColuna> char(1) default ''A'' not null'

Note that around the A are two bets and no double quotes.

Answer 2

Another way would be this:

/*Verifica se coluna já existe e se não houver insere com valor padrão 'A'*/
DECLARE
  va_existe integer;
  va_nome_tabela varchar2(100);
  va_nome_coluna varchar2(100);
BEGIN
  SELECT 1
    into va_existe
    FROM user_tab_columns
   WHERE table_name = va_nome_tabela
     AND column_name = va_nome_coluna;
exception
  when no_data_found then
    EXECUTE IMMEDIATE 'ALTER TABLE '||va_nome_tabela||' add '||va_nome_coluna||' char(1) default ''A'' not null';
  when others then
    raise_application_error(-20001, slqerrm);
END;

*As it is not possible to duplicate column names in a table, the query result will always be 1 record or no record (NO_DATA_FOUND); **The COMMIT command is not required after executing the EXECUTE IMMEDIATE command, as the command executed (by EXECUTE IMMEDIATE) is a DDL command that by default performs a COMMIT before it is executed.

Answer 3

After more researches to the colleague of the work hehe we managed to solve this way:

/*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;