11 Replies Latest reply: Jul 23, 2010 6:34 AM by 782404 RSS

    ORA-30678: too many open connections

    782404
      Hi,

      I executed this procedure..

      CREATE OR REPLACE procedure
      TRY.e_mail_message
      (
      from_name in varchar2,
      to_name in varchar2,
      subject in varchar2,
      message in varchar2
      )
      is
      l_mailhost VARCHAR2(64);
      l_from VARCHAR2(64);
      l_to VARCHAR2(64);
      crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
      l_mail_conn UTL_SMTP.connection;
      mesg VARCHAR2( 4000 );
      BEGIN

      select a.SERVER into l_mailhost from email_setting a where a.SERVER is not null;
      select a.USERNAME into l_from from email_setting a where a.SERVER is not null;
      l_from := from_name;

      --UTL_SMTP.open_data(l_mail_conn);
      dbms_output.put_line('email test ');
      mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
      'From: <'||l_from||'>' || crlf ||
      'Subject: ' ||subject|| crlf ||
      'To: '||to_name || crlf || '' || crlf ;
      mesg:=mesg||message;
      dbms_output.put_line(mesg);

      l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
      UTL_SMTP.helo(l_mail_conn, l_mailhost);
      UTL_SMTP.mail(l_mail_conn, l_from);
      UTL_SMTP.rcpt(l_mail_conn, to_name);
      UTL_SMTP.data(l_mail_conn, mesg);
      UTL_SMTP.quit(l_mail_conn);

      exception
      when others then dbms_output.put_line(sqlerrm);

      END;
      /

      However, email could not be sent to recipient. Below is the error message.


      Sending email to subject: Reminder to settle outstanding bil for Invoice No: CA/01062010/555
      call email_mssg cmd 3
      email test
      Date: 23 June 10 15:11:50
      From: <>
      Subject: Reminder to settle outstanding bil for Invoice No: CA/01062010/555
      To: customer@private.com

      Please Settle Outstanding Bil for your Invoice No: CA/01062010/555. Thank you
      ORA-30678: too many open connections

      Below is the username I keyed in the email_setting table, with reference to the select username statement from the procedure as above.

      username in email_setting => admin@private.com
      I also tried key in as 'admin@private.com' but still not working.


      Could someone suggest any solution? Thanks..