CLOB e ORA-06502 - Por que e como corrigir

Ontem eu estava depurando uma procedure Oracle que faz a importação de dados de um sistema para outro. Essa procedure de importação gera log de tudo: dados importados, dados rejeitados, motivo de rejeição e etc. O log é todo armazenado em uma variável CLOB, que ao final do processo é anexada ao registro que armazena a execução da importação - foi executado na data tal, e o resultado foi tal.

Ocorre que com o aumento da massa de dados a ser importada, começaram a aparecer erros ORA-06502 (PL/SQL: numeric or value error). Com a ajuda do PL/SQL Developer, consegui identificar que a exceção era gerada em uma linha que concatenava alguns valores na variável CLOB. Eu demorei algumas horas até me lembrar que uu já tinha visto esse erro devido à conversão implícita de string em número.

Nessas horas foram algumas pesquisas na internet, até que achei um blog que me deu algumas dicas e a memória clareou (vou ficar devendo o crédito pois perdi o endereço do blog original).

O fato é que na conversão implícita, o Oracle tenta converter tudo para NÚMERO. Isso é porque as operações com números (geralmente comparações, originadas de cláusulas WHERE) são muito mais rápidas que comparações de string. Só que apesar de mais rápido, é uma conversão menos segura, pois pode não funcionar em todos os casos. No nosso caso de CONCATENAÇÃO (e não de comparação), o Oracle tenta converter tudo para varchar, e nisso dá erro.

Vamos a comprovação: Um laço que a cada volta, concatena um número em um CLOB.

declare
    l_clob clob;
 
begin

    for i in 1..50000
    loop
        l_clob   := l_clob || 1;
        -- conversão implícita de número em varchar
        -- força a conversão do blob em varchar também.
    end loop;
    dbms_output.put_line (length(l_clob));

exception
    when others then
          dbms_output.put_line ('error: ' || sqlerrm);
          dbms_output.put_line (length(l_clob));
end;

Internamente o oracle converte esse CLOB em varchar, concatena com o 1 (também internamente já convertido em varchar), e armazena de volta na variável CLOB. Só que conforme a variável CLOB vai crescendo, chega um momento que fica maior que 32768 bytes (o limite de tamanho de varchar). Nessa hora, gera a exceção.

Agora veja o exemplo abaixo, quase igual ao anterior. A diferença é que agora a concatenação é de uma string em um CLOB. Nesse caso o Oracle não faz a conversão do CLOB em varchar, e portanto não viola o limite do varchar. Logo, não dá erro!

declare
    l_clob clob;
 
begin

    for i in 1..50000
    loop
        l_clob := l_clob || to_char(1);
        -- sem conversão implícita de número (ou datas),
        -- a operação é entre varchar e clob. Neste caso,
        -- não há conversão do clob em varchar.
    end loop;
    dbms_output.put_line (length(l_clob));

exception
    when others then
          dbms_output.put_line ('error: ' || sqlerrm);
          dbms_output.put_line (length(l_clob));
end;

É isso.
Espero que sirva para ajudar mais alguém por aí.

Abraços e até a próxima!