CREATE OR REPLACE PROCEDURE CHSI_DBA.SEND_EMAIL( SENDER_ADDRESS IN VARCHAR2, RECEIVER_ADDRESS IN VARCHAR2, SUBJECT IN VARCHAR2, CONTENTS IN VARCHAR2 ) IS EMAIL_SERVER VARCHAR2(30) := '1.1.1.1'; PORT NUMBER := 25; CONN UTL_SMTP.CONNECTION; MESG VARCHAR2(4000); BEGIN CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT); UTL_SMTP.HELO(CONN,EMAIL_SERVER); UTL_SMTP.MAIL(CONN,SENDER_ADDRESS); UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS); MESG:= 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=GB2312' || UTL_TCP.CRLF || 'DATE:' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || UTL_TCP.CRLF || 'FROM:' || SENDER_ADDRESS || UTL_TCP.CRLF || 'SUBJECT:' || SUBJECT || UTL_TCP.CRLF || 'TO: '|| RECEIVER_ADDRESS || UTL_TCP.CRLF || 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=GB2312' || UTL_TCP.CRLF || '' || UTL_TCP.CRLF || CONTENTS || UTL_TCP.CRLF ; UTL_SMTP.OPEN_DATA(CONN); UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG)); UTL_SMTP.CLOSE_DATA(CONN); UTL_SMTP.QUIT(CONN); END; /你需要在发送完毕之后,就及时关闭并退出
ORA-30678 too many open connectionsCause: An attempt to open a connection failed because too many are already open by this session. The number of allowed connections varies as some may be in use through other components which share the same pool of allowed connections.Action: Retry after closing some other connection. The number of connections supported is currently not adjustable.也就是说,需要先关闭一些,然后重试。 而且支持的最大连接数目前不能调整
你若是在循环中发送, 一定要及时UTL_SMTP.QUIT(CONN); 且要做异常处理 EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.INVALID_OPERATION OR UTL_SMTP.PERMANENT_ERROR THEN UTL_SMTP.QUIT(CONN);
SENDER_ADDRESS IN VARCHAR2,
RECEIVER_ADDRESS IN VARCHAR2,
SUBJECT IN VARCHAR2,
CONTENTS IN VARCHAR2
) IS
EMAIL_SERVER VARCHAR2(30) := '1.1.1.1';
PORT NUMBER := 25;
CONN UTL_SMTP.CONNECTION;
MESG VARCHAR2(4000);
BEGIN
CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT);
UTL_SMTP.HELO(CONN,EMAIL_SERVER);
UTL_SMTP.MAIL(CONN,SENDER_ADDRESS);
UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS);
MESG:= 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=GB2312' || UTL_TCP.CRLF ||
'DATE:' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || UTL_TCP.CRLF ||
'FROM:' || SENDER_ADDRESS || UTL_TCP.CRLF ||
'SUBJECT:' || SUBJECT || UTL_TCP.CRLF ||
'TO: '|| RECEIVER_ADDRESS || UTL_TCP.CRLF ||
'CONTENT-TYPE: TEXT/PLAIN; CHARSET=GB2312' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || CONTENTS || UTL_TCP.CRLF ;
UTL_SMTP.OPEN_DATA(CONN);
UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG));
UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);
END;
/你需要在发送完毕之后,就及时关闭并退出
而且支持的最大连接数目前不能调整
且要做异常处理
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.INVALID_OPERATION OR UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.QUIT(CONN);