Use variable in a run

Question

I’m creating the pl sequinte:

declare
  cont integer;
  tabela varchar2(100) := 'TABLE_TESTE';
  col varchar2(100) := 'TESTE';
begin
    SELECT COUNT(*) into cont FROM USER_TAB_COLUMNS
    WHERE TABLE_NAME = UPPER(tabela)
    AND COLUMN_NAME = UPPER(col);
   if cont = 0 then
       execute immediate 'ALTER TABLE tabela ADD col number(19,0)';
   end if;
end;
/

How can I use variables tabela and col in the execute immediate?

Answer 1

For DML commands and plsql blocks the immediate execute accepts parameters directly in the executed command. To set the value of the parameters the declaration is used using.

declare
  vID integer;
begin
  vId := '1233456';
  execute immediate 'Delete from UMA_TABELA where ID_TABELA = :ID' using vID;
end;

In your case, as a DDL command is not allowed to use parameters, in this case you need to concatenate your variables directly in the command. Ex:

execute immediate 'ALTER TABLE '|| tabela || ' ADD ' || col || ' number(19,0)';