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
Post Reply
rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Guys, good afternoon.

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:

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Below is a sample line of the file:

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
wmendes.miranda
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 11
Joined: Thu, 26 Aug 2010 10:51 am
Location: São Paulo
Contact:
Quer aprender PL/SQL de graça? acesse http://aprendaplsql.com/ /*EM CONTRUÇÃO*/

Hi Rsampaio, how are you?

The error "ORA-06502: PL / SQL: numeric or value error." It appears many times when the problem happens within a function / procedure that is being called by another that has SQLERRM in the treatment of errors, a tip that I give you to discover the real problem is you take that exception at the end of the code. It will give a treatment without treatment, but it will be the real error.

As for the problem in dbms_output can be with the version of your database, according to this Oracle documentation http://docs.oracle.com/cd/B19306_01/app ... tm#i999293, or use you use null in

Select all

DBMS_OUTPUT.ENABLE(NULL);
depending on From your version of the database it arrow with default the 20000 limit because some version does not accept unlimited buffer. ("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 90000 buffer I believe your problem will be solved, if it is not a look at SEND_Mail, if you want to send the code of this procedure, so we can help you more.

ABS and I hope I have helped.
William Miranda
rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Willian, thank you for the comment.

But in fact the error that occurs is this same: numeric or value error. This error handling I put it after I had this problem. I even took it back to test what you do, but the error remains the same.

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

I do not know what else to do ....
rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Does anyone have any light? :(
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I think we would have to see how that send_mail is sending emails.
maybe have a problem there (sometimes the 'content-type: text / html;' may be wrong, etc.)
rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Below is the code of procedure send_mail.

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; 
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Rsampaio,

All right?

Maybe the error you are encountering is directly related to the number of characters entered in your CloB field.

I do not know if it helps, but try to test with this example of the oracle-base site:

]
The article above appears to be very complete and explains well-formed methods for sending and email. It has even examples of e-mail with clob as an attachment.

The author of the article is British Tim Hall, which coincidently was one of the speakers of the Guob Tech Day this year.


Hugs and good luck,

Sergio Coutinho
rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Sergio speaks, thank you for your expensive comment, but the damn mistake continues! rs
I rode my procedure here with his email procedure and gave the same thing.

I'm pretty sure the problem is not with dbms_output and nor with the email sending procedure.
has any other problem that does not let this variable Clob get great.

Some Oracle configuration for variable size I know lá ... I do not know what else to do.
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

Rodrigo,

You have already tried using the package that I share in article http://www.fabioprado.net/2013/01/envia ... -de_9.html

I took a quick look at your code and I did not find the error . Try to identify in which line of code is occurring the error to know if the problem is in the procedure that sends the email or in the code where you fill in the var clob of the attachment, okay?
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

I think I'vê killed the charade.

If v_orchivo is of type clob, you can not use to_char when assigning values ??to it. You have to use to_clob. When you make to_char (v_arquivo) and the size of it passes 32767 (limit of varchar2), you will see the error.
Replace:

Select all

V_ARQUIVO := TO_CHAR(V_ARQUIVO) || CHR(13) || CHR(10) || TO_CHAR(V_CONTEUDO(I));
By:

Select all

V_ARQUIVO := V_ARQUIVO || TO_CLOB(CHR(13) || CHR(10)) || TO_CLOB(V_CONTEUDO(I));
test and give me a return .


rsampaio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Wed, 07 Aug 2013 4:02 pm

Fábio, perfect guy! I did not know that there was this to_clob!
Thanks for the help!

Big hug.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 18 guests