PL / SQL SMTP: 501 Error: attribute value too long

Question

PL / SQL SMTP: 501 Error: attribute value too long

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I am presented with an error when creating an automatic email through the database, I am creating the body and I get this error message:

ORA-29279: Permanent SMTP error: 501 Error: attribute value too long

ORA-06512: in "SYS.UTL_SMTP", line 17

ORA-06512: in "SYS.UTL_SMTP", line 98

ORA-06512: in "SYS.UTL_SMTP", line 317

ORA-06512: online 2

What is the problem? How can I solve that? This is my code:

  inicioColTitulo               VARCHAR(3000) :='<td align="center"><font face="Arial" color="FFFFFF" size="2">';
  finColTitulo                  VARCHAR(3000) :='</font></td>';
  tituloTabla                   VARCHAR(3000) :='Vencimiento Importaciones Temporales a Corto Plazo';
  inicioCol                     VARCHAR(3000) :='<td bgcolor="#e9f3fc" align="center"><font face="Arial" color="#000000" size="1" style="vertical-align: bottom">';
  finCol                        VARCHAR(3000) :='</font></td>';

BEGIN

 OPEN nit_cursor 

    FOR select distinct(v.ID_CLIENTE)
          from V_CONTROL_22 v;

   LOOP FETCH nit_cursor

    INTO id_nit;
    EXIT WHEN nit_cursor%NOTFOUND;  

    mesg_body :=              '<html>';
    mesg_body := mesg_body ||   '<head>';
    mesg_body := mesg_body ||       '<title>Email</title>';
    mesg_body := mesg_body ||   '</head>';
    mesg_body := mesg_body ||   '<script>';
    mesg_body := mesg_body ||       'resizeTo(300,300);';
    mesg_body := mesg_body ||   '</script>';
    mesg_body := mesg_body ||   '<body bgcolor="#FFFFFF" link="#000080">';

    SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') INTO mi_fecha FROM DUAL;
    mi_cadena := trim(            '<center>');
    mi_cadena := trim(mi_cadena ||  '<table border="0">');
    mi_cadena := trim(mi_cadena ||    '<tr>');
    mi_cadena := trim(mi_cadena ||      '<td>');
    mi_cadena := trim(mi_cadena ||        '<table border="1">');
    mi_cadena := trim(mi_cadena ||          '<tr bgcolor="000080">');
    mi_cadena := trim(mi_cadena ||            '<td colspan="10" align="center">');
    mi_cadena := trim(mi_cadena ||              '<b>');
    mi_cadena := trim(mi_cadena ||               '<font face="Arial" color="FFFFFF" size="3">');
    mi_cadena := trim(mi_cadena ||                 tituloTabla || '<br>');
    mi_cadena := trim(mi_cadena ||                 '*** Fecha Ejecución: ' || mi_fecha || ' *** ');
    mi_cadena := trim(mi_cadena ||               '</font>');
    mi_cadena := trim(mi_cadena ||              '</b>');
    mi_cadena := trim(mi_cadena ||             '</td>');
    mi_cadena := trim(mi_cadena ||            '</tr>');
    mi_cadena := trim(mi_cadena ||          '<tr bgcolor="000080">');
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);
    mi_cadena := trim(mi_cadena || inicioColTitulo || 'Columna No. 1' || finColTitulo);     
    mi_cadena := trim(mi_cadena ||'</tr>');


    OPEN correo_cursor 
     FOR SELECT GCT.CA_CORREO_ELECTRONICO
         FROM GEN_CONTACTOS GCT
         WHERE GCT.ID_IDENTIFICACION = id_nit
         AND GCT.DO_CARGO_FUNCIONAL = 'RL'; 

     LOOP FETCH correo_cursor

         INTO emailCliente;
         EXIT WHEN correo_cursor%NOTFOUND;

         IF(correosCliente IS NULL) THEN

             correosCliente := emailCliente;

         ELSE

             correosCliente := correosCliente || ';' || emailCliente;

         END IF;

     END LOOP;

     CLOSE correo_cursor; 

     if (correosCliente IS NOT NULL) THEN

     conn:= utl_smtp.open_connection( EmailServer, Port );   
     utl_smtp.helo( conn, EmailServer );
     utl_smtp.mail( conn, '[email protected]');            




     FOR otrosCorreos IN(SELECT AUS.CA_DIRECCION_CORREO empl
                           FROM ADM_USUARIOS AUS, GEN_FICHA_TECNICA GFTD
                         WHERE GFTD.ID_IDENTIFICACION_FUN = AUS.ID_IDENTIFICACION
                           AND GFTD.ID_CONSECUTIVO_FICHA = 22
                           AND SYSDATE BETWEEN GFTD.FE_INICIAL AND GFTD.FE_FINAL)

       LOOP

             utl_smtp.rcpt(conn,otrosCorreos.empl);

           IF(correosDir IS NULL) THEN

               correosDir := otrosCorreos.empl;

           ELSE

               correosDir := correosDir || ';' || otrosCorreos.empl;

           END IF;

       END LOOP; 


     correos := correosDir;

     mesg:= 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
            'From: '||'[email protected]'|| crlf ||
            'Subject: Vencimiento Documentos' || crlf ||
            'To:   '||correos|| crlf ||
            '' || crlf ||mesg_body||'';

     utl_smtp.OPEN_DATA(conn);
     utl_smtp.WRITE_DATA(conn,'MIME-Version: 1.0' ||crlf||'Content-type: text/html' ||crlf||mesg);
     UTL_SMTP.WRITE_DATA(conn,mi_cadena);

     FOR datos IN(SELECT NU_DO,
                         ID_CLIENTE,
                         NO_CLIENTE,
                         NO_PEDIDO,
                         NO_FAC,
                         MODALIDAD,
                         ACEPTACION_DIM,
                         FE_DIM,
                         FE_LEVANTE_INICIAL,
                         FE_VENCIMIENTO,
                         ID_DOCUMENTO_DO,
                         ID_DOCUMENTO_DEC
                    FROM V_CONTROL_22 V
                   WHERE V.ID_CLIENTE = id_nit )
     LOOP

      mi_cadena := trim('<tr bgcolor="#e9f3fc">');
      mi_cadena := trim(mi_cadena || inicioCol || datos.NU_DO              || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.ID_CLIENTE         || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.NO_CLIENTE         || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.NO_PEDIDO          || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.NO_FAC             || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.MODALIDAD          || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.ACEPTACION_DIM     || finCol);          
      mi_cadena := trim(mi_cadena || inicioCol || datos.FE_DIM             || finCol);
      mi_cadena := trim(mi_cadena || inicioCol || datos.FE_LEVANTE_INICIAL || finCol);            
      mi_cadena := trim(mi_cadena || inicioCol || datos.FE_VENCIMIENTO     || finCol);                      
      mi_cadena := trim(mi_cadena || '</tr>');                        
      UTL_SMTP.WRITE_DATA(conn,mi_cadena);

     END LOOP;

     mi_cadena  := trim('</table></td></tr></table></center>');                       
     UTL_SMTP.WRITE_DATA(conn,mi_cadena);

     mi_cadena := mi_cadena || trim( '<div style="font-family:verdana; text-align:justify;">'); 
     mi_cadena := mi_cadena || trim(     '<div>');
     mi_cadena := mi_cadena || trim(         '... Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(         '<ol>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? . ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(         '</ol>');
     mi_cadena := mi_cadena || trim(         ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(         '<ol type="a">');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(             '<li>');
     mi_cadena := mi_cadena || trim(                 ' Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ');
     mi_cadena := mi_cadena || trim(             '</li>');
     mi_cadena := mi_cadena || trim(         '</ol>');
     mi_cadena := mi_cadena || trim(         '<p><strong>loren loren 1</strong>  Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? . </p>');
     mi_cadena := mi_cadena || trim(         '<p><strong>loren loren 1</strong>  Lorem ipsum dolor sit amet, consectetur adipisicing elit. Incidunt nesciunt doloremque velit laboriosam laborum, earum labore odit reprehenderit temporibus hic totam quam, ad officia voluptates mollitia. At, officia minus expedita? ...');
     mi_cadena := mi_cadena || trim(         '</p>');
     mi_cadena := mi_cadena || trim(     '</div>');
     mi_cadena := mi_cadena || trim( '</div></body></html>');
     UTL_SMTP.WRITE_DATA(conn,mi_cadena);      

     utl_smtp.CLOSE_DATA(conn);
     utl_smtp.quit(conn);
     correosDir:= NULL;
     correosCliente := NULL;
     mi_cadena := NULL;
     END IF;

 END LOOP;

 CLOSE nit_cursor;       

END pr_Consulta;

sql oracle plsql

asked by Gdaimon 11.02.2016 в 20:45

source

3 answers

Conversion error NSData in NSString How to traverse array values after inserting a value in a busy place

score 1 · Answer 1

Your server does not support any of the attributes because it is too long, you use loops to save data in mi_cadena , I also notice that you use for multiline data:

UTL_SMTP.WRITE_DATA(conn,mi_cadena);

Which is correct.

But remember that the restriction for UTL_SMTP.WRITE_DATA () is that should not be greater than 32K (VARCHAR2).

Error 501 is usually caused by incorrect or malformed data sent to the server.

score 1 · Answer 2

Error 29279 corresponds to error in sending and 501 is basically syntax:

The command was correct and recognized, but the parameters (for example, the email address arguments) are not valid

It can be due to several causes, including some as simple as not separating the emails with commas, the domain of an email is incorrect, not placing the angled supports <[email protected]> or a very long email address etc.

It would be interesting if you did the query to the DB limiting just one email, that you know is correct, in order to discard these theories and go the other way.

I leave a document where all the SMTP Status Codes appear, look for the 501 because there are several possibilities that you should discard: [ link ], Hopefully this helps in some way.

score 0 · Answer 3

Maybe you should revise the size you assign to that VARCHAR2 field. In case the maximum is not enough and you still want to save the string you should save the text as a byte array like BLOB or some similar data type and then capture it and convert it to text again. I have done this before to store XML structures read from an .xml file and it has been useful to save it as a byte array with a BLOB data type.

This is the size in bytes that BLOB supports: 2,147,483,647 bytes (2GB) if a size is not specified in the creation of the column