I have a problem in a procedure that stores text information and then sends by email to an attachment file. The purpose of the procedure I did is: from the contents of a query in a view, I will include line the line in a cloble type variable and then need to send this content to an email with the attached file (txt).
All that I said above is working, however, with a restriction. The original query returns 150 lines, but if I leave it like this, the error appears: ORA-06502: PL / SQL: numeric or value error.
If I limit the consultation result in 50 lines, the entire procedure works perfectly.
The error appears if I use the mail send routine and also appears if I use a simple dbms_output.put_line.
The contents of this TXT file has more or less, in each line, 600 characters.
Can anyone give me a hand? The procedure follows:
DECLARE
TYPE V_CONTEUDO_ARRAY IS TABLE OF CLOB;
V_CONTEUDO V_CONTEUDO_ARRAY;
V_ARQUIVO CLOB;
V_STATUS INTEGER;
V_LINHA LONG;
V_TESTE BOOLEAN;
NOME_ARQUIVO VARCHAR2(50);
CURSOR CONTEUDO IS
SELECT CONTEUDO
FROM VW_INTEGRACAO_GOVRJ
WHERE ROWNUM <= 50
ORDER BY ORDEM;
BEGIN
OPEN CONTEUDO;
FETCH CONTEUDO BULK COLLECT
INTO V_CONTEUDO;
CLOSE CONTEUDO;
V_TESTE := TRUE;
DBMS_OUTPUT.ENABLE(NULL);
FOR I IN 1 .. V_CONTEUDO.COUNT LOOP
IF V_TESTE = TRUE THEN
V_ARQUIVO := V_CONTEUDO(I);
V_TESTE := FALSE;
V_STATUS := V_CONTEUDO.COUNT;
ELSE
V_ARQUIVO := V_ARQUIVO || CHR(13) || CHR(10) || V_CONTEUDO(I);
END IF;
END LOOP;
SEND_MAIL(V_FROM => 'xxxxxx',
V_RECIPIENT => 'yyyyyy',
V_SUBJECT => 'assunto teste',
V_BODY => 'conteudo teste',
V_NOME_ARQUIVO => NOME_ARQUIVO,
V_CONTEUDO_ARQUIVO => V_ARQUIVO);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception, Quantidde de Linhas = ' || V_STATUS);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(V_LINHA);
END;