ORA-06502: PL/SQL: numeric or value error

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 13 Aug 2013 12:34 pm

Guys, good afternoon.

I have a problem in a procedure that stores text and then sends information by e-mail on an enclosed file. The purpose of the procedure I did is: from the contents of a query on a view, including line-by-line in a variable of type CLOB and then need to send that content in an e-mail message with the attached file (txt).

Everything that I said above is working, however, with a restriction. The original query returns 150 lines but if I leave it at that, pops up the error: ORA-06502: PL/SQL: numeric or value error.

If I limit the query result in 50 lines, the whole procedure works perfectly.
The error pops up if I use the routine of sending e-mail and appears also if I use a simple Dbms_output.put_line.PUT_LINE.

The content of this txt file has more or less, on each line, 600 characters.
I wonder if anyone can give me a hand? Follow the procedure below:
Code: Select all
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;
rsampaio

Poston Tue, 13 Aug 2013 12:40 pm

Below is a sample line from the file:
Code: Select all
20679     A12S042417     CLIENTE XX                                        SOARES DE MIRANDA                                           00000                         BAIRRO1                       292234920001660000285000029500000000100000000015000000000042,87000000000042,87000000000000,00000000000000000000000000000,00000000000000,40000000000000,00000000000086,14130525130400                                                                      00000127296120000000000000000000000000000000000000000000000000000000000000000000 0000000000 0000000000 0000000000 0000000000 0000000000 00000000129041330031308130000000000000000,00
rsampaio

Poston Tue, 20 Aug 2013 10:18 am

Hi rsampaio, how are you?

The " error ORA-06502: PL/SQL: numeric or value error. " pops up often when the problem happens inside a function/procedure that is being called by another that owns the SQLERRM in handling errors, a tip I give you to discover the real problem is you get this EXCEPTION at the end of the code. Will give an error without treatment, but will be the real error.

As for the problem may be with the version Dbms_output.put_line in its database, according to this Oracle documentation http://docs.oracle.com/cd/B19306_01/app ... tm#i999293, when you use the NULL in
Code: Select all
DBMS_OUTPUT.ENABLE(NULL);
depending on your version of the database he arrow with default 20000 limit, because some do not accept unlimited buffer version. (NULL " is expected to be the usual choice. The default is 20.000 for backwards compatibility with earlier database versions that did not support unlimited buffering. ").

You have an average of 600 and 150 records if you set a buffer of 90000 believe that your problem will be solved, if he doesn't get a look in SEND_MAIL, if you want to send code to this procedure, so we can help you more.

abs and I hope I helped.
William Miranda
wmendes.miranda
Location: São Paulo

Quer aprender PL/SQL de graça? acesse http://aprendaplsql.com/ /*EM CONTRUÇÃO*/

Poston Tue, 03 Sep 2013 7:42 pm

William, thanks for the comment.

But actually the error that occurs is that same numeric or value error. This error handling I put after that had this problem. Even took again to test what you said, but the error is still the same.

Another thing is that no matter how I use the field that will receive the contents of the file, it always of this error. Using the procedure of email or the DBMS, the error is the same.

I don't know what else to do. ...
rsampaio

Poston Wed, 18 Sep 2013 10:16 am

Does anyone have any light? :(
rsampaio

Poston Tue, 24 Sep 2013 10:48 am

I think we have to see how this SEND_MAIL ta sending e-mail.
Maybe there's a problem there (sometimes the ' content-type: text/html; ' may be wrong, etc)
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 25 Sep 2013 9:03 am

Below is the code procedure SEND_MAIL.

Code: Select all
CREATE OR REPLACE PROCEDURE SEND_MAIL(V_FROM             IN VARCHAR2,
                                      V_RECIPIENT        IN VARCHAR2,
                                      V_SUBJECT          IN VARCHAR2,
                                      V_MAIL_HOST        IN VARCHAR2 DEFAULT,
                                      V_SMTP_PORT        IN NUMBER DEFAULT 25,
                                      V_BODY             IN VARCHAR2,
                                      V_NOME_ARQUIVO     IN VARCHAR2,
                                      V_CONTEUDO_ARQUIVO IN CLOB) IS

   L_MAIL_CONN UTL_SMTP.CONNECTION;
   CRLF        VARCHAR2(2) := CHR(13) || CHR(10);

BEGIN

   L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, V_SMTP_PORT);
   UTL_SMTP.HELO(L_MAIL_CONN, V_MAIL_HOST);
   UTL_SMTP.MAIL(L_MAIL_CONN, V_FROM);
   UTL_SMTP.RCPT(L_MAIL_CONN, V_RECIPIENT);

   IF (LENGTH(V_NOME_ARQUIVO) > 0) THEN
      UTL_SMTP.DATA(L_MAIL_CONN,
                    'Date: ' || TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || CRLF ||
                     'From: ' || V_FROM || CRLF || 'Subject: ' || V_SUBJECT || CRLF ||
                     'To: ' || V_RECIPIENT || CRLF ||
                   
                     'MIME-Version: 1.0' || CRLF || -- Use MIME mail standard
                     'Content-Type: multipart/mixed;' || CRLF ||
                     ' boundary="-----SECBOUND"' || CRLF || CRLF ||
                   
                     '-------SECBOUND' || CRLF || 'Content-Type: text/plain;' || CRLF ||
                     'Content-Transfer_Encoding: 7bit' || CRLF || CRLF || V_BODY || CRLF || -- Message body
                    --'more message text' || CRLF || CRLF ||
                     '-------SECBOUND' || CRLF || 'Content-Type: text/plain;' || CRLF ||
                     ' name="' || V_NOME_ARQUIVO || '"' || CRLF ||
                     'Content-Transfer_Encoding: 8bit' || CRLF ||
                     'Content-Disposition: attachment;' || CRLF || ' filename="' ||
                     V_NOME_ARQUIVO || '"' || CRLF || CRLF || V_CONTEUDO_ARQUIVO || CRLF || -- Content of attachment
                     CRLF ||
                   
                     '-------SECBOUND--'
                    -- End MIME mail
                    );
   ELSE
      UTL_SMTP.DATA(L_MAIL_CONN,
                    'Date: ' || TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || CRLF ||
                     'From: ' || V_FROM || CRLF || 'Subject: ' || V_SUBJECT || CRLF ||
                     'To: ' || V_RECIPIENT || CRLF ||
                   
                     'MIME-Version: 1.0' || CRLF || -- Use MIME mail standard
                     'Content-Type: multipart/mixed;' || CRLF ||
                     ' boundary="-----SECBOUND"' || CRLF || CRLF ||
                   
                     '-------SECBOUND' || CRLF || 'Content-Type: text/plain;' || CRLF ||
                     'Content-Transfer_Encoding: 7bit' || CRLF || CRLF || V_BODY || CRLF -- Message body
                    );
   END IF;
   UTL_SMTP.QUIT(L_MAIL_CONN);
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
      RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
   
END SEND_MAIL;
rsampaio

Poston Thu, 26 Sep 2013 11:46 pm

Rsampaio, okay?

Maybe the error you're experiencing is directly related to the number of characters entered in the CLOB field.

I don't know if it helps, but try doing a test with this example the site of ORACLE-BASE : http://www.oracle-base.com/articles/mis ... -plsql.php the article above seems to be very thorough and explains so well the didactic methods for sending and email. Even has examples of emails with CLOB as an attachment.

The author of the article is Briton TIM HALL, which coincidentally was one of the speakers at this year's TECH DAY GUOB.


Hugs and good luck, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Fri, 27 Sep 2013 5:27 pm

Says Sergio, thanks for your comment dude, but the error continues. rs I rode my procedure here with the procedure of sending e-mail and gave the same thing.

I am pretty sure that the problem isn't with the Dbms_output.put_line and not with the procedure of sending e-mail.
Is there any other problem that won't let this variable CLOB stay great.

Any Oracle configuration for variable size know lá ... I don't know what else to do.
rsampaio

Poston Wed, 02 Oct 2013 8:45 am

Rodrigo, you'vê tried to use the package that share in the article http://www.fabioprado.net/2013/01/envia ... -de_9.html ?

I gave a quick look at your code and found the error. Try to identify which line of code is the error occurring so we know if the problem is in the procedure that sends the email or in the code where you fills the var clob annex, ok?
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Wed, 02 Oct 2013 1:50 pm

I think I'vê killed the charade.

If V_ARQUIVO is of type CLOB, you cannot use TO_CHAR when assigning values to it. You have to use TO_CLOB. When you make TO_CHAR (V_ARQUIVO) and her size is 32767 (limit of VARCHAR2), you will see the error.

Replace:
Code: Select all
V_ARQUIVO := TO_CHAR(V_ARQUIVO) || CHR(13) || CHR(10) || TO_CHAR(V_CONTEUDO(I));
:
Code: Select all
V_ARQUIVO := V_ARQUIVO || TO_CLOB(CHR(13) || CHR(10)) || TO_CLOB(V_CONTEUDO(I));
test and get back to me.

[] S
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Wed, 02 Oct 2013 2:24 pm

Fabio, perfect guy! I didn't know there was this TO_CLOB!
Thank you very much for your help!

Big Hug.
rsampaio


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 8 guests