Inserting column in Oracle table with conditional

Navigation
0

I need to create a column in an existing table but need to do with a check condition if this column already exists in the database I did this way however 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 error log: 

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:
    
asked by anonymous 08.04.2015 / 19:38

3 answers

0

After more research to colleague hehe we have been able 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;
    
09.04.2015 / 13:30
1

Speak 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: line 12, column 88

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

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

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

Note that around A are two apostrophes and not double quotes.

    
08.04.2015 / 19:57
0

Another way would be as follows: 

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

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

    
01.06.2017 / 18:36
How to use string created string.xml via code How to use DataFixtureLoader