Exemplo de PL/SQL com uso de execute immediate

add_log.sql

DECLARE
    permissao_existe number(4);
    log_anterior_existe number(4);
    responsavel_id number(8);
    usuario_id number(8);
    setor_id number(8);
    tipo_permissao varchar(30 CHAR);
    id_ccod_objeto varchar(18 CHAR);
    motivo varchar(100 BYTE);
    dataHoraAgora date;   
    ultimo_registro_log schema_dominio.tb_log_permissao%ROWTYPE;
BEGIN
    select sysdate into dataHoraAgora from dual;
    motivo := '';
    responsavel_id := 16960;
    usuario_id := 16960;
    setor_id := 10001;
    tipo_permissao := 'Atendente';
    select lpad('42'||'1234567890'||trunc(dbms_random.value()*1000000),18,0) into id_ccod_objeto from dual;
    
    -- verifica se permissão já existe
    execute immediate 'select count(ccod_objeto) from schema_dominio.tb_' || tipo_permissao || ' where ccod_objeto_credencial=:usuario_id and ccod_objeto_setor=:setor_id'
        into permissao_existe USING usuario_id, setor_id ;
    if permissao_existe = 0 then 
       -- registra permissão
       execute immediate  'insert into schema_dominio.tb_' || tipo_permissao || '(CCOD_OBJETO,CCOD_OBJETO_CREDENCIAL,CCOD_OBJETO_SETOR) values (:id_ccod_objeto, :usuario_id, :setor_id)'
          USING id_ccod_objeto, usuario_id, setor_id;
       DBMS_OUTPUT.PUT_LINE('Concedendo permissão...');
       
       -- verifica se permissão foi registrada corretamente
       execute immediate  'select count(ccod_objeto)  from schema_dominio.tb_' || tipo_permissao || ' where ccod_objeto_credencial=:usuario_id and ccod_objeto_setor=:setor_id'
          into permissao_existe USING usuario_id, setor_id;
        
       if permissao_existe > 0 then     
            DBMS_OUTPUT.PUT_LINE('Permissão concedida');
            
            -- verifica se existe registro anterior no log
            select count(*) into log_anterior_existe from schema_dominio.tb_log_permissao
                where csgl_tipo = tipo_permissao and ccod_objeto_servidor = usuario_id and  ccod_objeto_setor = setor_id;
    
            if log_anterior_existe > 0 then
               -- busca registro anterior no log
               select * into ultimo_registro_log from schema_dominio.tb_log_permissao
                   where ccod_objeto_atual = 1 and csgl_tipo = tipo_permissao and ccod_objeto_servidor = usuario_id and  ccod_objeto_setor = setor_id;
    
               -- efetua novo registro no log
               insert into schema_dominio.tb_log_permissao(CCOD_OBJETO,CSGL_TIPO,CCOD_OBJETO_SERVIDOR,CNOM_SERVIDOR,CCOD_OBJETO_SETOR,CSGL_SETOR,CNOM_SETOR,CCOD_OBJETO_RESP_ATRIBUICAO,CNOM_RESP_ATRIBUICAO,DDAT_ATRIBUICAO,CCOD_OBJETO_RESP_REMOCAO,CNOM_RESP_REMOCAO,DDAT_REMOCAO,CCOD_OBJETO_PERM_ANT,CCOD_OBJETO_ATUAL,CTXT_MOTIVO_ATRIBUICAO,CTXT_MOTIVO_REMOCAO)
                    values (id_ccod_objeto,tipo_permissao, usuario_id, (select nome from schema_autenticador.usuarios where id=usuario_id), setor_id, (select sigla from schema_autenticador.setores where id=setor_id), (select nome from schema_autenticador.setores where id=setor_id), responsavel_id, (select nome from schema_autenticador.usuarios where id=responsavel_id), dataHoraAgora, null, null, null,
                        ultimo_registro_log.ccod_objeto, 1, motivo, null);
               DBMS_OUTPUT.PUT_LINE('Registrado no log');
               
               -- atualiza registro anterior no log
               update schema_dominio.tb_log_permissao set CCOD_OBJETO_ATUAL = 0 where ccod_objeto = ultimo_registro_log.ccod_objeto;
               DBMS_OUTPUT.PUT_LINE('Atualizado registro anterior no log');
            else 
               -- efetua novo registro no log
               insert into schema_dominio.tb_log_permissao(CCOD_OBJETO,CSGL_TIPO,CCOD_OBJETO_SERVIDOR,CNOM_SERVIDOR,CCOD_OBJETO_SETOR,CSGL_SETOR,CNOM_SETOR,CCOD_OBJETO_RESP_ATRIBUICAO,CNOM_RESP_ATRIBUICAO,DDAT_ATRIBUICAO,CCOD_OBJETO_RESP_REMOCAO,CNOM_RESP_REMOCAO,DDAT_REMOCAO,CCOD_OBJETO_PERM_ANT,CCOD_OBJETO_ATUAL,CTXT_MOTIVO_ATRIBUICAO,CTXT_MOTIVO_REMOCAO)
                    values (id_ccod_objeto,tipo_permissao, usuario_id, (select nome from schema_autenticador.usuarios where id=usuario_id), setor_id, (select sigla from schema_autenticador.setores where id=setor_id), (select nome from schema_autenticador.setores where id=setor_id), responsavel_id, (select nome from schema_autenticador.usuarios where id=responsavel_id), dataHoraAgora, null, null, null,
                        null, 1, motivo, null);
                DBMS_OUTPUT.PUT_LINE('Registrado no log');                    
            end if;
        else
            DBMS_OUTPUT.PUT_LINE('Falha ao conceder permissão');
        end if;
       
    else
       DBMS_OUTPUT.PUT_LINE('Permissão já existe');
    end if;

    commit;
END;