Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

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

    hi,

    I run this procedure:

    CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
    destinatario1 IN varchar2,
    object IN varchar2,
    messagge IN varchar2) IS

    cursor my_cur is
    select text
    from my_tab;

    mailhost varchar2(40):= 'pop3.cxx.yyyyyyyyy';

    conn utl_smtp.connection;
    crlf varchar2(2):=CHR(13)||CHR(10);
    messag long; --varchar2(3000);
    v_mitt varchar2(2000) := mitt;
    BEGIN

    conn := utl_smtp.open_connection (mailhost, 25);

    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1;

    for rec_msg in my_cur
    loop
    messag := messag||chr(10)||rec_msg.text;
    end loop;
    utl_smtp.helo(conn, mailhost);
    utl_smtp.mail (conn, v_mitt);
    utl_smtp.rcpt (conn, destinatario1);
    utl_smtp.data(conn, messag);
    utl_smtp.quit(conn);
    EXCEPTION
    when utl_smtp.transient_error or utl_smtp.permanent_error then
    utl_smtp.quit(conn);
    raise_application_error (-20000, 'error: '||sqlerrm);
    END send_mail;


    this procedure run correctly if the table my_tab has less of 50 records, but when it has more 50 records I get this error:

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.STANDARD", line 342
    ORA-06512: at "SYS.UTL_TCP", line 359
    ORA-06512: at "SYS.UTL_SMTP", line 263
    ORA-06512: at "SYS.UTL_SMTP", line 234
    ORA-06512: at "SYS.UTL_SMTP", line 241
    ORA-06512: at "AFM.CESS_IMM_NEW2", line 720
    ORA-06512: at "AFM.CESS_IMM_NEW2", line 755

    The table my_tab has one column text (before varchar2 (1000)) now long

    I believe that problem is here:
    messag long; --varchar2(3000);
    messag := messag||chr(10)||rec_msg.text;

    because with little char the procedure run correctly and send in one mail each record of table "my_tab"

    How can I send more characters in mail??

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    rather use write_data etc.

    data Function
    This function specifies the body of an e-mail message.

    Syntax
    UTL_SMTP.DATA (
    c IN OUT NOCOPY connection
    body IN OUT NOCOPY)
    RETURN reply;
    UTL_SMTP.DATA (
    c IN OUT NOCOPY connection
    body IN OUT NOCOPY);

    Parameters
    Table 100-13 data Function Parameters
    Parameter Description
    c (IN OUT NOCOPY)
    The SMTP Connection.

    body (IN OUT NOCOPY)
    The text of the message to be sent, including headers, in [RFC822] format.


    Usage Notes
    The application must ensure that the contents of the body parameter conform to the MIME(RFC822) specification. The data() routine will terminate the message with a <CR><LF>.<CR><LF> sequence (a single period at the beginning of a line), as required by RFC821. It will also translate any sequence of <CR><LF>.<CR><LF> (single period) in body to <CR><LF>..<CR><LF> (double period). This conversion provides the transparency as described in Section 4.5.2 of RFC821.

    The data() call should be called only after open_connection(), helo() / ehlo(), mail() and rcpt() have been called. The connection to the SMTP server must be open, and a mail transaction must be active when this routine is called.

    The expected response from the server is a message beginning with status code 250. The 354 response received from the initial DATA command will not be returned to the caller.

    open_data(), write_data(), write_raw_data(), close_data() Functions
    These APIs provide more fine-grain control to the data() API; in other words, to the SMTP DATA operation. open_data() sends the DATA command. After that, write_data() and write_raw_data() write a portion of the e-mail message. A repeat call to write_data() and write_raw_data() appends data to the e-mail message. The close_data() call ends the e-mail message by sending the sequence <CR><LF>.<CR><LF> (a single period at the beginning of a line).

    Syntax
    UTL_SMTP.OPEN_DATA (
    c IN OUT NOCOPY connection)
    RETURN reply;
    UTL_SMTP.OPEN_DATA (
    c IN OUT NOCOPY connection);
    UTL_SMTP.WRITE_DATA (
    c IN OUT NOCOPY connection,
    data IN OUT NOCOPY);
    UTL_SMTP.WRITE_RAW_DATA (
    c IN OUT NOCOPY connection
    data IN OUT NOCOPY);
    UTL_SMTP.CLOSE_DATA (
    c IN OUT NOCOPY connection)
    RETURN reply;
    UTL_SMTP.CLOSE_DATA (
    c IN OUT NOCOPY connection);

    Parameters
    Table 100-14 open_data(), write_data(), write_raw_data(), close_data() Function Parameters
    Parameter Description
    c (IN OUT NOCOPY)
    The SMTP connection.

    data (IN OUT NOCOPY)
    The portion of the text of the message to be sent, including headers, in [RFC822] format.

    have a look at the description of the utl_smtp-Packkage at:

    http://download-uk.oracle.com/docs/c...12/u_smtp2.htm

    I would rather recommend to use the write_data function in a loop ...

    ----------

    Usage Notes
    The calls to open_data(), write_data(), write_raw_data() and close_data() must be made in the right order. A program calls open_data() to send the DATA command to the SMTP server. After that, it can call write_data() or write_raw_data() repeatedly to send the actual data. The data is terminated by calling close_data(). After open_data() is called, the only APIs that can be called are write_data(), write_raw_data(), or close_data(). A call to other APIs will result in an INVALID_OPERATION exception being raised.

    The application must ensure that the contents of the body parameter conform to the MIME(RFC822) specification. The data() routine will terminate the message with a <CR><LF>.<CR><LF> sequence (a single period at the beginning of a line), as required by RFC821. It will also translate any sequence of <CR><LF>.<CR><LF> (single period) in the body to <CR><LF>..<CR><LF> (double period). This conversion provides the transparency as described in Section 4.5.2 of RFC821.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •