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.
Nenhum comentário:
Postar um comentário