I'm creating the following pl:
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 the variables tabela and col in execute immediate ?
For DML commands and plsql blocks the execute immediate accepts parameters directly in the executed command. To set the value of the parameters the declaration using is used.
declare
vID integer;
begin
vId := '1233456';
execute immediate 'Delete from UMA_TABELA where ID_TABELA = :ID' using vID;
end;
In your case, because 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)';