Results 1 to 10 of 10

Thread: Need help

  1. #1
    Join Date
    Sep 2008
    Posts
    10

    Unanswered: Need help

    i want to send email to my client and cc to my boss. this email will inform my client about their treatment. To run this email, i created a procedure which is automatically run fullfil the condition. but when try run this procedure, i got error. The error as below.

    SQL> execute mailfromdb4('gu_kaya80@yahoo.com');
    BEGIN mailfromdb4(gu_kaya80@yahoo.com); END;

    *
    ERROR at line 1:
    ORA-29279: SMTP permanent error: 555 Unsupported option: Medical
    ORA-06512: at "SYS.UTL_SMTP", line 17
    ORA-06512: at "SYS.UTL_SMTP", line 98
    ORA-06512: at "SYS.UTL_SMTP", line 221
    ORA-06512: at "SYS.MAILFROMDB4", line 40
    ORA-06512: at line 1

    So i need help how to solve this error.

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Please include the code for the procedure. It will be much easier to diagnose the problem if we can see the source.

  3. #3
    Join Date
    Sep 2008
    Posts
    10
    Quote Originally Posted by buckeye234
    Please include the code for the procedure. It will be much easier to diagnose the problem if we can see the source.
    Hi,
    thanks for your help. Attached is the code for the procedure as below..

    CREATE or replace PROCEDURE MAILFROMDB4 ( MESSAGE IN VARCHAR) AS

    cursor c1 is select mailid.send_recpt,mailids,order_no from mailid,mailid_order
    where Mailid.SEND_RECPT = Mailid_order.SEND_RECPT order by order_no;

    conn UTL_SMTP.CONNECTION;

    crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

    mesg VARCHAR2( 4000 );

    mesg_body varchar2(4000);

    AdminMailid varchar2(30);

    vToList varchar2(2000);

    vCcList varchar2(2000);

    vBccList varchar2(2000);

    vSenderEmail varchar2(2000);

    BEGIN

    /* Open connection */

    conn:= utl_smtp.open_connection( 'mail.com', 25 );

    /* Hand Shake */

    utl_smtp.helo( conn, 'mail.com' );

    /* Loop for configure sender and recipient to UTL_SMTP */

    for c1rec in c1 loop

    if c1rec.send_recpt = 'SENDER' then

    utl_smtp.mail(conn, c1rec.mailids);

    vSenderEmail := c1rec.mailids;

    else

    utl_smtp.rcpt(conn, c1rec.mailids );

    end if;

    /* Making a TO list */

    if upper(c1rec.send_recpt) = 'TO' then

    vTolist := vToList || c1rec.mailids||';';

    end if;

    /* Making a CC list */

    if upper(c1rec.send_recpt) = 'CC' then

    vCclist := vCcList || c1rec.mailids||';';

    end if;

    /* Making a BCC list */

    if upper(c1rec.send_recpt) = 'BCC' then

    vBcclist := vBccList || c1rec.mailids||';';

    end if;

    end loop;

    /* making a message buffer */

    mesg:=



    'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||

    'From: '||vSenderEmail|| crlf ||

    'Subject: Mail Through ORACLE Database' || crlf ||

    'To: '||VToList|| crlf||

    'Cc: '||VCcList||crlf ||

    'Bcc: '||VBccList||crlf||crlf||

    ' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||

    'It is very easy to configure Tell me if you face any problems'||crlf||message ;

    /* Configure sending message */

    utl_smtp.data( conn, mesg );

    /* closing connection */

    utl_smtp.quit( conn );

    END;


    also command to execute this procedure

    execute mailfromdb4('gu_kaya80@yahoo.com');

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, which of them is
    "SYS.MAILFROMDB4", line 40
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2008
    Posts
    10
    Quote Originally Posted by n_i
    So, which of them is
    hi n_i,

    i run this procedure using id SYS.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What I meant to ask was this: if you actually read the error message, it says that the error originates on line 40 of the SYS.MAILFROMDB4 procedure. Since you didn't bother to preserve formatting of the procedure when pasting it here, you would need to tell us which is the line 40 in the original procedure source. Once we know this, we can proceed further.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Sep 2008
    Posts
    10
    Quote Originally Posted by n_i
    What I meant to ask was this: if you actually read the error message, it says that the error originates on line 40 of the SYS.MAILFROMDB4 procedure. Since you didn't bother to preserve formatting of the procedure when pasting it here, you would need to tell us which is the line 40 in the original procedure source. Once we know this, we can proceed further.
    hi n_i,

    i think the error on line 40 refer to the statement as i highlighted below

    for c1rec in c1 loop

    if c1rec.send_recpt = 'SENDER' then

    utl_smtp.mail(conn, c1rec.mailids);
    vSenderEmail := c1rec.mailids;

    else

    utl_smtp.rcpt(conn, c1rec.mailids );

    end if;

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Now all you need to do is to check your database tables and make sure there is only one SENDER and it contains a single valid email address.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Sep 2008
    Posts
    10
    Quote Originally Posted by n_i
    Now all you need to do is to check your database tables and make sure there is only one SENDER and it contains a single valid email address.
    hi n_i,

    ic..ok
    i will try it first and update u as soon as possible..

  10. #10
    Join Date
    Sep 2008
    Posts
    10
    hi n_i,

    thanks a lot..now it's works..
    i have checked data in single table. before this i set mailids in my single table as Medical Section, that's why i got error. Now i changed to Medical_section@yahoo.com, and now it's works.

    So my problem was resolved. Thanks n_i..

Posting Permissions

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