Field greater than long

[Kleito Cesar]

Good afternoon staff,

I have a procedure that generates a dynamic HTML and another procedure that sends email.

But in some cases HTML becomes huge (he is already well customized), which at the time I am generating this HTML store in a field of type Long and when it arrives at a certain point this Field bursts.

Does anyone know if you have any other field larger than long and that I can manipulate string's, or any other solution?

Thanks

[dr_gori]

Long is something from "Véia Arco" ... There is still still for compatibility purposes with ancient versions.

"Things of Modernity" are BLOB or Clob fields ... Another important reading is about DBMS_LOB !!

[Kleito Cesar]

Thank you Dr_Gori.

And which of these would be more interesting for this case?

[Trevisolli]

Brother, if it is only text, could store a clob .

[dr_gori]

That!
Clob is for text
Blob is for binary

[Kleito Cesar]

Good ..
What I did was just exchange the Long CLOB field statement.

And it does the manipulation right without appearing errors, until the procedure call that sends the email.

Call of procedure:



procedure

Select all

 
 
PROMPT CREATE OR REPLACE PROCEDURE gdoc_prc_envia_email 
CREATE OR REPLACE PROCEDURE gdoc_prc_envia_email( P_REMETENTE IN VARCHAR2, 
                                                  P_NOME_REMETENTE IN VARCHAR2, 
                                                  P_DESTINATARIO IN VARCHAR2, 
                                                  P_ASSUNTO IN VARCHAR2, 
                                                  P_MENSAGEM   IN CLOB 
                                                  ) IS 
 
/* P_ORIGEM   - Email de quem está enviando                   P_REMETENTE 
   P_DESTINO  - Email de quem vai receber 
   P_ASSUNTO  - Assunto do email 
   P_TEXTO    - Texto do email */ 
 
  MAILHOST VARCHAR2(30) := 'xxx'; -- NOME OU IP DO SERVIDOR SMTP 
  MAILPORT NUMBER(4)    :=25;           -- PORTA SMTP 
  MAIL_CONN UTL_SMTP.CONNECTION; 
  WDS_USER VARCHAR2(30)    :=  'usuario'; 
  WDS_PASSWORD VARCHAR2(9) := 'senha'; 
  CRLF VARCHAR2(2)         := CHR(13)||CHR(10); 
 
 
 BEGIN 
        MAIL_CONN:= utl_smtp.open_connection(MAILHOST, MAILPORT); 
        utl_smtp.helo(MAIL_CONN,  MAILHOST); 
        utl_smtp.command (MAIL_CONN,  'AUTH LOGIN'); 
        utl_smtp.command (MAIL_CONN,  utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((WDS_USER ))))); 
        utl_smtp.command (MAIL_CONN,  utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((WDS_PASSWORD)))));   --> senha 
 
        UTL_SMTP.MAIL (MAIL_CONN,  ('<' || P_REMETENTE || '>')); /* E-mail de quem está mandando */ 
        UTL_SMTP.RCPT (MAIL_CONN,  ('<' || P_DESTINATARIO|| '>')); /* Para quem vou mandar */ 
        -- 
        utl_smtp.open_data(MAIL_CONN); 
        UTL_SMTP.WRITE_RAW_DATA( MAIL_CONN, UTL_RAW.CAST_TO_RAW('From:'    ||P_NOME_REMETENTE||'<'|| P_REMETENTE|| '>' || utl_tcp.CRLF)); 
        UTL_SMTP.WRITE_RAW_DATA( MAIL_CONN, UTL_RAW.CAST_TO_RAW('To:'      ||P_DESTINATARIO||utl_tcp.CRLF)); 
        UTL_SMTP.WRITE_RAW_DATA( MAIL_CONN, UTL_RAW.CAST_TO_RAW('Subject:' ||P_ASSUNTO||utl_tcp.CRLF)); 
        UTL_SMTP.WRITE_RAW_DATA( MAIL_CONN, UTL_RAW.CAST_TO_RAW('Content-Type: text/html; charset=iso-8859-1'||utl_tcp.CRLF)); 
        -- 
        utl_smtp.write_data(MAIL_CONN, ' '||utl_tcp.CRLF); 
 
        utl_smtp.write_raw_data(MAIL_CONN, utl_raw.cast_to_raw(utl_tcp.CRLF||P_MENSAGEM)); 
        -- 
        UTL_SMTP.CLOSE_DATA(MAIL_CONN); 
        UTL_SMTP.QUIT (MAIL_CONN); 
  EXCEPTION 
    WHEN OTHERS THEN 
        UTL_SMTP.QUIT (MAIL_CONN); 
        -- 
END; 
/ 

The error:

Select all

 
ORA-29277: operação SMTP inválida 
ORA-06512: em "SYS.UTL_SMTP", line 43 
ORA-06512: em "SYS.UTL_SMTP", line 149 
ORA-06512: em "SYS.UTL_SMTP", line 358 
ORA-06512: em "SYS.UTL_SMTP", line 374 
ORA-06512: em "GEDOC_DESENV.GDOC_PRC_ENVIA_EMAIL", line 45 
ORA-06502: PL/SQL: erro numérico ou de valor 
ORA-06512: em "GEDOC_DESENV.GDOC_ENVIA_EMAIL_APONTAMENTO", line 294 
ORA-06512: em line 1 

[dr_gori]

What exactly is this line 45 in your code?

Select all

ORA-06512: em "GEDOC_DESENV.GDOC_PRC_ENVIA_EMAIL", line 45 
ORA-06502: PL/SQL: erro numérico ou de valor 

[Kleito Cesar]

It is well on the procedure exception.

If I comment the exception the error will happen in the following line:

Select all

        utl_smtp.write_raw_data(MAIL_CONN, utl_raw.cast_to_raw(P_MENSAGEM)); 

Obs. P_message is the CloB field.

[Trevisolli]

Brother,

Try in this way:

Select all

 
      utl_smtp.write_data(MAIL_CONN, P_MENSAGEM);  

[Kleito Cesar]

This error occurs when performing the procedure:

PLS-00306: Incorrect number of argument types in the call to 'WRITE_RAW_DATA'
This should be because it does not Accepts the cloble type as a parameter.

[Trevisolli]

Brother,

Try only WRITE_DATA, rather than WRITE_RAW_DATA:

Select all

 
  utl_smtp.write_data(MAIL_CONN, P_MENSAGEM);  

[Kleito Cesar]

Continues giving the same error of before before ..

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

But I noticed the following:
As I said before this error happens in certain situations where the text extends a lot, but with the type of the CloB field does not give problem at the time of manipulation of the text, apparently it is correct, the problem happens only when the field is Past of parametro for UTL_SMTP and the CloB field is very extensive.

So I believe it is probably the UTL_SMTP that is not standing out the data.

Is this meaning?
Do you have any way out?

Thanks.

[dr_gori]

Tom Kyte made an example where he sends the data to UTL_SMPT from 1900 at 1900 bytes.
See:
[url=http://asktom.oracle.com/pls/asktom/f?p ... 9411218448]link
Another example: http://forums.oracle.com/forums/thread. ... 8&tstart=0

[Kleito Cesar]

\ o / worked.
Thank you guys.

I decided to delete the email sending procedure, and add the partitioned text to the UTL_SMTP.

Thanks.

[rsampaio]

Guys, good afternoon.

I am basically the same problem as our friend who created this post. 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 someone 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;

[dr_gori]

Remember that dbms_output.put_line only supports 255 characters.
More than that makes mistake.
See: http://asktom.oracle.com/pls/asktom/f?p ... 6412348066
Is not it taking another mistake before that? (and exception is generating another).

[rsampaio]

Gori,

I'm not using DBMS in this procedure, I just made a test to know if the problem was with the email sending procedure.

in relation to the masked error by Exception, I also think it is not because I took exception from the procedure and he returned the same error.

[mih_cns]

Hello I'm going through a similar problem.
I want to send an e-mail with attachment, but the attachment only comes out the HTML.
The result of the SELECT even does not leave.
Some help?

Select all

     -- Envia o e-mail  
 
     DECLARE 
   v_From       VARCHAR2(80) := V_E_MAIL_ORIGEM; 
   v_Recipient  VARCHAR2(80) := V_E_MAIL_DESTINO; 
   v_Subject    VARCHAR2(200) := V_E_MAIL_ASSUNTO_1; 
   v_Mail_Host  VARCHAR2(30) := 'smtp-appl1.senado.gov.br'; 
   v_Mail_Conn  utl_smtp.Connection; 
   crlf         VARCHAR2(2)  := chr(13)||chr(10); 
    
BEGIN 
  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); 
 
  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); 
 
  utl_smtp.Mail(v_Mail_Conn, v_From); 
 
  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); 
 
  utl_smtp.Data(v_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 || 
    'some message text'|| crlf ||   -- Message body 
    'more message text'|| crlf || 
    crlf || 
 
    '-------SECBOUND'|| crlf || 
    'Content-Type: text/plain;'|| crlf || 
    ' name="excel.csv"'|| crlf || 
    'Content-Transfer_Encoding: 8bit'|| crlf || 
    'Content-Disposition: attachment;'|| crlf || 
    ' filename="excel.csv"' || crlf || 
    crlf || 
    V_TEXTO_EMAIL|| crlf ||   -- Content of attachment 
    crlf || 
 
    '-------SECBOUND--'         -- End MIME mail 
  ); 
 
  utl_smtp.Quit(v_mail_conn); 
EXCEPTION 
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then 
    raise_application_error(-20000, 'Unable to send mail: '||sqlerrm); 
END; 
                
               

[maxwbh]

The error occurs because you should partition the BLOB field.
See the example:

Select all

 
        v_length := dbms_lob.getlength(p_attach_blob); 
        <<while_loop>> 
        WHILE v_offset < v_length LOOP 
            dbms_lob.read(p_attach_blob, l_step, v_offset, v_raw); 
            utl_smtp.write_raw_data(vconexao, utl_encode.base64_encode(v_raw)); 
            utl_smtp.write_data(vconexao, utl_tcp.crlf); 
            v_offset := v_offset + l_step; 
        END LOOP while_loop;

The complete code is in: https://github.com/Maxwbh/SendMail/