Social Icons

19 de out de 2010

Ressetando Sequências no Oracle

Pessoal,

     Uma problema muito comum que os desenvolvedores que trabalham com Bancos de Dados Oracle enfrentam, ao testar suas aplicações em ambientes de Desenvolvimento e Homologação, é a impossibilidade de ressetar ou redefinir valores de sequências (sequences) após terminar testes que necessitam limpar ou voltar o estado anterior dos dados afetados.
    
     No Oracle, as sequências não podem ser ressetadas ou redefinidas para um determinado valor. Para ressetar (determinar ou reatribuir o valor inicial), por exemplo, uma sequência que tem o valor de início 1, que termina em 1000 e que tem o valor atual de 353, o único meio é configurar reciclagem de valores e chamar o método NEXTVAL da sequence inúmeras vezes até que ela chegue ao número final (1000) + 1.

     Como este processo é bem trabalhoso, muitos DBA´s apenas apagam e recriam as sequências, o que é não é um método muito seguro, pois neste processo, ao apagar a sequência, todos os usuários que possuíam privilégios de acesso sobre ela, perdem estes privilégios. Para evitar este problema e facilitar o trabalho dos desenvolvedores e/ou DBA´s, podemos criar a procedure abaixo, dar privilégio de EXECUTE nela para os desenvolvedores que precisam ressetar suas sequências e depois instruí-los a chamar esta procedure passando apenas o nome do owner da sequência e o nome da própria sequência.
        Ex.: BEGIN
                SP_SEQUENCE_RESET(SCHEMA_NAME, SEQUENCE_NAME);
             END;

               
--- ínicio do código da procedure
create or replace PROCEDURE SP_SEQUENCE_RESET(schema_name VARCHAR2, sequence_name VARCHAR2)
AUTHID current_user is
BEGIN
--------------------------------------------------------------------------------
--  SP_SEQUENCE_RESET
--------------------------------------------------------------------------------
-- PARÂMETROS
--------------------------------------------------------------------------------
-- entrada: schema_name ->  nome do schema proprietário da sequence a ser resetada
-- entrada: sequence_name -> nome da sequence a ser resetada
--------------------------------------------------------------------------------
-- Criação: 19/10/2010
-- Autor: Fábio Prado
-- Descrição: Procedure para ressetar sequences
-- Detalhes: Utiliza os privilégios do usuário que está chamando a procedure (AUTHID current_user).
-- O usuário que chama a procedure deve ter privilégios para selecionar e alterar a sequence informada.
--------------------------------------------------------------------------------
    DECLARE plast_number number;
            PCYCLE_FLAG CHAR(1);
            pmin_value NUMBER;
            pmax_value NUMBER;
            psequence_name VARCHAR2(30);
            powner_name VARCHAR2(30);
            pstrSQL VARCHAR2(1000);
            pseq_number number;
    BEGIN
        BEGIN       
            powner_name:=UPPER(schema_name);
            psequence_name:=UPPER(sequence_name);          

            BEGIN
            --1) Testa sequência para ver se ela já foi inicializada, para evitar erro ORA-08002
            PSTRSQL:='SELECT ' || POWNER_NAME || '.' || PSEQUENCE_NAME || '.CURRVAL FROM DUAL';
            --dbms_output.put_line(pstrSQL);

            EXECUTE IMMEDIATE pstrSQL INTO pseq_number;
            EXCEPTION
              WHEN OTHERS THEN
                -- Entra nesse erro quando sequence já estiver no valor limite
                IF INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-08004') <> 0 THEN
                  -- nao faz nada, só trata erro para continuar processamento
                  NULL;
                END IF;            
            END;

            --2 ) Verifica qual o último valor registrado na seqüência com a seguinte instrução.
            SELECT  MIN_VALUE, MAX_VALUE, CYCLE_FLAG, LAST_NUMBER
            into    pmin_value, pmax_value, pcycle_flag, plast_number
            from    all_sequences d
            where   d.sequence_owner = UPPER(powner_name)
            AND     d.sequence_name = UPPER(psequence_name);

            dbms_output.put_line('Valor atual da sequência: ' || (plast_number));
 
            --3) se número atual da sequence é maior que maxvalue entao iguala valores
            IF plast_number > pmax_value THEN
              pmax_value:= plast_number;
            END IF;

            --4) Altera o minvalue para zero e maxvalue para o mesmo valor encontrado no last_value no resultado da instrução acima.
            pstrSQL:='alter sequence ' || powner_name || '.' || psequence_name || ' minvalue 0 maxvalue ' || plast_number || ' cycle';
            dbms_output.put_line(pstrSQL);
            EXECUTE IMMEDIATE pstrSQL;

            --5) Executa o Nextval múltiplas vezes até valor da sequencia for igual valor inicial
            WHILE (pseq_number <> pmin_value) LOOP
                PSTRSQL:='SELECT ' || POWNER_NAME || '.' || PSEQUENCE_NAME || '.NEXTVAL FROM DUAL';
                --dbms_output.put_line(pstrSQL);
                EXECUTE IMMEDIATE pstrSQL INTO pseq_number;       
            END LOOP;           

            --6) Reatribui valores originais de minvalue, maxvalue e cache da sequencia
            IF PCYCLE_FLAG = 'N' THEN
                pstrSQL:='alter sequence  ' || powner_name || '.' || psequence_name || ' minvalue ' || pmin_value || ' maxvalue ' || pmax_value || '  nocycle';
            ELSE
                pstrSQL:='alter sequence  ' || powner_name || '.' || psequence_name || ' minvalue ' || pmin_value || ' maxvalue ' || pmax_value;
            END IF;
            dbms_output.put_line(pstrSQL);
            EXECUTE IMMEDIATE pstrSQL;   

            DBMS_OUTPUT.PUT_LINE('Procedimento executado com sucesso');
            COMMIT;
        END;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Procedimento cancelado: O nome da sequência ' || powner_name || '.' || psequence_name || ' não existe.');
        WHEN OTHERS THEN
            IF INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-08002') <> 0 OR
                INSTR(UPPER(SQLERRM(SQLCODE)),'ORA-04004') <> 0 THEN
                dbms_output.put_line('Procedimento cancelado: Não foi possível ressetar a sequência ' || powner_name || '.' || psequence_name || ' não inicializada.');
            ELSE
                dbms_output.put_line('Erro ao ressetar a sequência ' || powner_name || '.' || psequence_name || ': ' || SQLERRM);
            END IF;
    END;
END;


    Obs.: A procedure, após executada, não altera as configurações (cache, order, cycle etc.) da SEQUENCE. Para ressetar uma sequência é necessário que o usuário que está chamando a procedure tenha privilégios de SELECT e ALTER na sequência informada.

0 comentários:

Postar um comentário

 

Meus últimos Links Favoritos

Suporte remoto alunos

Seguidores

Meu One Drive (antigo Sky Drive)