problemas com procedure para atualizar sequences

[Tmontes]

Amigos notei um pequeno bug na view USER_TRIGGER_COLS,

Bolei uma procedure para adequar as sequences ao valor do campo a que ela se referencia usando a USER_TRIGGER_COLS, (uso uma trigger para que todo campo adicionado receba o valor da sequence)

na procedure eu dropo e recrio a sequence novamente,
só que apos fazer isso a view USER_TRIGGER_COLS aponta para todas as colunas da tabela que ela faz referencia, e não somente ao comapo relacionado a trigger então depois de rodar a primeira vez minha procedure não consigo mais rodar, alguém já passou por isso? fui claro na minha duvida?

abs!

[fsitja]

Cara, não entendi o problema.
Pelo que você falou, a view deveria ter pelo menos uma linha cuja coluna da sua sequence possua "column_usage" = "new out" (ou "new in out" se ela for usada para alguma outra coisa).

Porém, a trigger só faz isso? Só um comando ":new.sua_coluna := sua_sequence.nextval"?

Se ela usar outros :old ou :new eles vão aparecer também.

Explica melhor o que sua procedure faz, porque não entendi o que você quis dizer na frase abaixo. O que você quer dizer com "adequar"?

Bolei uma procedure para adequar as sequences ao valor do campo a que ela se referencia usando a USER_TRIGGER_COLS

[fsitja]

Por adequar você quer dizer dropar e recriar a sequence com start with a partir de um select max da coluna ou algo semelhante?

[fsitja]

umm, na atribuição, quero dizer

Selecionar tudo

select sequence.nextval into ttttt from dual

[fsitja]

Rodei um teste aqui, dropando e recriando a sequence via procedure e dando o start with com o select max() da coluna. A view continua mostrando correto. Pode ser algum fator influenciando, aí não sei dizer. De repente é a versão do Oracle também. Se quiser posso botar o código que fiz pra testar, só está meio bagunçado...

Selecionar tudo

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

[Marcelo Malta]

Pessoal,

Estou criando uma nova sequence em uma tabela que já existe no sistema. Coloquei toda a codificação necessária no Forms. Entretanto eu preciso atualizar a sequence com o valor atual do campo que está sendo utilizado. Eu não tenho acesso ao banco de produção, por isso preciso criar um script ou uma rotina para tal.

Fiz algo do tipo, mas dá erra na linha do execute immediate. Alguém pode me ajudar?

Selecionar tudo

PROCEDURE Corrige_PK IS
	MaxTabela	    number;
	ValorAtual	    number;
BEGIN
  select max(cd_tabela)
  Into MaxTabela
  from tabela;

  select lpad(s_tabela.nextval,2,'0') into ValorAtual from dual;
  MaxTabela := MaxTabela - ValorAtual;
  
  IF MaxTabela >= 1 THEN
   	EXECUTE immediate 'alter sequence s_ara_tabela_preço increment by MaxTabelaPreco';
    select lpad(s_tabela.nextval,2,'0') from dual;
    commit;
  END IF;

  EXECUTE immediate 'alter sequence s_ara_tabela_preço increment by 1';
  commit;
END;

Abraços

[fsitja]

Olá,

Bom, você não postou a mensagem de erro que ocorre, mas creio que seja o famoso ORA-01031: insufficient privileges.

Isso ocorre porque procedures executam por default com os privilégios do owner da procedure, o que se chama de definer's rights (DR).

O owner da sua procedure deve possuir system privilege (grant) para alterar sequence, e esse grant precisa ser direto, sem ser por role.

Outra alternativa seria mudar sua procedure para invoker's rights (IR), que quer dizer que ele vai ter as permissões do usuário de produção que executa a procedure, e não mais do owner dela. Isso depende do usuário que executa ela ter o grant de alter sequence.

Como isso envolve uma série de questões de segurança e permissão, talvez fosse muito mais simples fazer um loop com o .nextval várias vezes com increment padrão da sequence, sem fazer alter sequence...

Independentemente da solução adotada, recomendo ler a documentação a respeito de invoker's e definer's rights, que vem a calhar quando for pensada novas soluções desse tipo:

http://download.oracle.com/docs/cd/E118 ... #CNCPT1777

http://download.oracle.com/docs/cd/E118 ... LNPLS00809

[Marcelo Malta]

Obrigado pela dica dos grants, mas acredito que não tenha problema com isso ainda...

O problema é na compilação da procedure.
O erro é o seguinte:

Selecionar tudo

Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ;
The symbol ":= before "IMMEDIATE" to continue.

Abraços

[fsitja]

Eu estou olhando para o código mas não estou achando o problema que você menciona. Se for possível postar um copy/paste da compilação no SQL*Plus, fica muito mais fácil de localizar, pois diz o número da linha com erro e tudo mais.

De toda forma, há a chance de resolver o erro de compilação e cair no erro de execução que mencionei, por conta de estar usando SQL dinâmico e DDL dentro do programa PL/SQL. Lembre-se que o DDL vai mandar um commit implícito para o programa executando, caso você tenha uma transação em aberto, já era, está commitada sem volta.

[fsitja]

Só para deixar mais claro, os 2 comandos de Commit; que você colocou ali no PL/SQL não servem para nada e já são feitos pelo próprio Alter Sequence, pois eles são DDL, logo não apenas não necessitam de commit como eles próprios contêm um commit "oculto", que pode causar efeitos colaterais nocivos se essa procedure for chamada por um outro programa que não saiba disso.

[Marcelo Malta]

Na verdade são dois erros nas linhas do execute IMMEDIATE.

Entretanto aquela outra procedure que eu passei funcionou.

Obrigado!