Fórum Procedure com BULK COLLECT limitado. #619702

Deu certo, muito obrigado!! Não tinha pensado nessa opção, anteriormente tentei fazer o TYPE utilizando a tabela de destino, mas não tinha dado certo, também.

Complementando a resposta anterior, referente à correção do erro, segue uma sugestão de boas práticas.

Seu código prevê que o dado seja copiado em blocos, uma vez que utiliza um loop, mas apresenta mais dois erros:

1 - Não prevê um critério de parada.

Um fetch após o último registro lido retorna 0 linhas, mas não aborta um loop/end loop. É necessário interromper o loop com uma checagem condicional, do tipo:

exit when v_teste.COUNT = 0;

2 - Não foi imposto um limite ao tamanho do fetch.

Caso o cursor retorne 1 bilhão de linhas, o banco tentará fazer o fetch de 1 bilhão de linhas de uma só vez. Isto não aumenta o desempenho e põe em risco a estabilidade da instância, seja pelo consumo excessivo de memória, tempo da transação ou consumo de undo.

O ideal é limitar o fetch, para garantir que as transações seja eficientes, mas pequenas e rápidas, evitando efeitos colaterais.

O objetivo do bulk collect/insert é minimizar o chaveamento de contexto entre a aplicação PL/SQL e o banco. A documentação sugere que utilizar blocos de 30 linhas pode ser suficiente, já que reduziria o chaveamento de contexto em aproximadamente 97%.

Eu costumo fazer uma estimativa do número de blocos afetados, utilizando um número aproximado de 100 blocos. Desta forma, em um insert como este, você preencherá completamente cerca de 99 blocos e o último terá algum espaço livre, que será preenchido pelo próximo insert. Isto faz com que o processamento duplicado de um bloco caia para cerca de 1% e você garante uma transação de 800 KB, que é bem pequena, rápida e libera os locks.

Ex.: Se o seu insert gera linhas de aproximadamente 150 bytes, o bloco utiliza 8 KB e a tabela foi configurada com PCT_FREE 10, por exemplo, cada bloco poderá conter: (8192 * 0.9)/150 ~49 linhas. Neste caso, eu utilizaria o fetch com um limite de 4900 ou 5000 linhas, para ficar redondo.

FETCH c_TESTE BULK COLLECT INTO v_teste limit 5000;