oracle发送邮件 通过oracle发送邮件 请问我如何通过oracle发送邮件如用户名:aaa 密码:111请问如何实现???? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 转发的:使用oracle发送邮件 【IT168 服务器学院】通过在存储过程中调用此存储过程来监控存储过程中所产生的错误,以便尽快解决。 CREATE OR REPLACE PROCEDURE send_mail ( p_recipient IN VARCHAR2, p_message IN VARCHAR2, p_subject IN VARCHAR2 DEFAULT 'Oracle Perf Report ' || TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss' ), p_sender IN VARCHAR2 DEFAULT '[email protected]' ) IS v_mailhost VARCHAR2 (30) := 'test.sina.com'; --SMTP服务器地址 mail_conn UTL_SMTP.connection; msg VARCHAR2 (4000); p_user VARCHAR2 (30) := '[email protected]'; --登录SMTP服务器的用户名 p_pass VARCHAR2 (30) := 'test'; --登录SMTP服务器的密码 BEGIN /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */ msg := 'Date:' || TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss') || UTL_TCP.crlf || 'From: ' || p_sender || '<' || p_sender || '>' || UTL_TCP.crlf || 'To: ' || p_recipient || '<' || p_recipient || '>' || UTL_TCP.crlf || 'Subject: ' || p_subject || UTL_TCP.crlf || UTL_TCP.crlf || p_message; -- dbms_output.put_line(msg); mail_conn := UTL_SMTP.open_connection (v_mailhost, 25); UTL_SMTP.helo (mail_conn, v_mailhost); /* smtp服务器登录校验 */ 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 (p_user) ) ) ); UTL_SMTP.command (mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass) ) ) ); UTL_SMTP.mail (mail_conn, p_sender); UTL_SMTP.rcpt (mail_conn, p_recipient); /* 发送数据 */ UTL_SMTP.DATA (mail_conn, msg); UTL_SMTP.quit (mail_conn); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); NULL; END send_mail; 转发: http://publish.it168.com/2006/0205/20060205006301.shtml 又找到一个连接: http://www.51log.net/dev/6102/4462445.htm 以下是我的过程:CREATE OR REPLACE PROCEDURE send_mail1(p_recipient IN VARCHAR2 DEFAULT '[email protected]',p_message IN VARCHAR2 DEFAULT 'The data is ',p_subject IN VARCHAR2 DEFAULT 'Oracle Report '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'),p_sender IN VARCHAR2 DEFAULT '[email protected]')ISv_mailhost VARCHAR2 (30) := 'smtp.sina.com.cn'; mail_conn UTL_SMTP.connection;msg VARCHAR2 (4000);p_user VARCHAR2 (30) := 'sxlcom';p_pass VARCHAR2 (30) := '000928' ;BEGIN msg :='Date:'||TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')|| UTL_TCP.crlf|| 'From: '|| p_sender|| '<'|| p_sender|| '>'|| UTL_TCP.crlf|| 'To: '|| p_recipient|| '<'|| p_recipient|| '>'|| UTL_TCP.crlf|| 'Subject: '|| p_subject|| UTL_TCP.crlf|| UTL_TCP.crlf|| p_message;--|| p_content;--dbms_output.put_line(msg);mail_conn := UTL_SMTP.open_connection (v_mailhost, 25);UTL_SMTP.helo (mail_conn, v_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 (p_user))));UTL_SMTP.command (mail_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass))));UTL_SMTP.mail (mail_conn, p_sender);UTL_SMTP.rcpt (mail_conn, p_recipient); UTL_SMTP.DATA (mail_conn, msg);UTL_SMTP.quit (mail_conn);--EXCEPTION WHEN OTHERS-- THEN-- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);-- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);NULL;END send_mail1;/我执行会报如下错误,请问如何解决???先谢了SQL> exec send_mail2BEGIN send_mail2; END;*ERROR 位于第 1 行:ORA-29279: SMTP 永久性错误: 501 Bad address syntaxORA-06512: 在"SYS.UTL_SMTP", line 17ORA-06512: 在"SYS.UTL_SMTP", line 98ORA-06512: 在"SYS.UTL_SMTP", line 221ORA-06512: 在"DSTRANS.SEND_MAIL2", line 50ORA-06512: 在line 1 字符问题已经处理,编译可通过,但发不了邮件。CREATE OR REPLACE PROCEDURE send_mail ( p_recipient IN VARCHAR2, p_message IN VARCHAR2, p_subject IN VARCHAR2 DEFAULT 'Oracle Perf Report ' || TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss' ), p_sender IN VARCHAR2 DEFAULT '[email protected]')IS v_mailhost VARCHAR2 (30) := 'mail.longshine.com';--SMTP服务器地址 mail_conn UTL_SMTP.connection; msg VARCHAR2 (4000); p_user VARCHAR2 (30) := '[email protected]'; --登录SMTP服务器的用户名 p_pass VARCHAR2 (30) := 'test'; --登录SMTP服务器的密码BEGIN /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */ msg := 'Date:' || TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss') || UTL_TCP.crlf || 'From: ' || p_sender || '<' || p_sender || '>' || UTL_TCP.crlf || 'To: ' || p_recipient || '<' || p_recipient || '>' || UTL_TCP.crlf || 'Subject: ' || p_subject || UTL_TCP.crlf || UTL_TCP.crlf || p_message; -- dbms_output.put_line(msg); mail_conn := UTL_SMTP.open_connection (v_mailhost, 25); UTL_SMTP.helo (mail_conn, v_mailhost); /* smtp服务器登录校验 */ 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 (p_user) ) ) ); UTL_SMTP.command (mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass) ) ) ); UTL_SMTP.mail (mail_conn, p_sender); UTL_SMTP.rcpt (mail_conn, p_recipient); /* 发送数据 */ UTL_SMTP.DATA (mail_conn, msg); UTL_SMTP.quit (mail_conn);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); NULL;END send_mail; ORACLE XML 过长 请问在oracle中有半角与全角字符串转换的内置函数? 一个难题!这里有人能否帮忙解决? 请问一分类合并统计语句 连接oracle数据库出了问题 如何随机选取n条记录或者对记录作随机排序? 问题真难,高手来帮助一下吧!送1000分都可以。 在那里可以下载到Oracle10G SQL语句 怎样排序字符串类型的字段 请帮忙将sqlserver上的SQL语句转换成oracle的标准语句 求一条SQL语句,一棵树,只要求把叶子找出来。
使用oracle发送邮件
【IT168 服务器学院】通过在存储过程中调用此存储过程来监控存储过程中所产生的错误,以便尽快解决。 CREATE OR REPLACE
PROCEDURE send_mail (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2 DEFAULT 'Oracle Perf Report '
|| TO_CHAR (SYSDATE,
'yyyy-mm-dd hh24:mi:ss'
),
p_sender IN VARCHAR2 DEFAULT '[email protected]'
)
IS
v_mailhost VARCHAR2 (30) := 'test.sina.com';
--SMTP服务器地址
mail_conn UTL_SMTP.connection;
msg VARCHAR2 (4000);
p_user VARCHAR2 (30) := '[email protected]';
--登录SMTP服务器的用户名
p_pass VARCHAR2 (30) := 'test'; --登录SMTP服务器的密码
BEGIN
/* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
msg :=
'Date:'
|| TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')
|| UTL_TCP.crlf
|| 'From: '
|| p_sender
|| '<'
|| p_sender
|| '>'
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| '<'
|| p_recipient
|| '>'
|| UTL_TCP.crlf
|| 'Subject: '
|| p_subject
|| UTL_TCP.crlf
|| UTL_TCP.crlf
|| p_message;
-- dbms_output.put_line(msg);
mail_conn := UTL_SMTP.open_connection (v_mailhost, 25);
UTL_SMTP.helo (mail_conn, v_mailhost);
/* smtp服务器登录校验 */
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 (p_user)
)
)
);
UTL_SMTP.command
(mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass)
)
)
);
UTL_SMTP.mail (mail_conn, p_sender);
UTL_SMTP.rcpt (mail_conn, p_recipient);
/* 发送数据 */
UTL_SMTP.DATA (mail_conn, msg);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
NULL;
END send_mail;
http://publish.it168.com/2006/0205/20060205006301.shtml
http://www.51log.net/dev/6102/4462445.htm
CREATE OR REPLACE PROCEDURE send_mail1
(p_recipient IN VARCHAR2 DEFAULT '[email protected]',
p_message IN VARCHAR2 DEFAULT 'The data is ',
p_subject IN VARCHAR2 DEFAULT 'Oracle Report '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'),
p_sender IN VARCHAR2 DEFAULT '[email protected]')
IS
v_mailhost VARCHAR2 (30) := 'smtp.sina.com.cn';
mail_conn UTL_SMTP.connection;
msg VARCHAR2 (4000);
p_user VARCHAR2 (30) := 'sxlcom';
p_pass VARCHAR2 (30) := '000928' ;
BEGIN
msg :=
'Date:'
||TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')
|| UTL_TCP.crlf
|| 'From: '
|| p_sender
|| '<'
|| p_sender
|| '>'
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| '<'
|| p_recipient
|| '>'
|| UTL_TCP.crlf
|| 'Subject: '
|| p_subject
|| UTL_TCP.crlf
|| UTL_TCP.crlf
|| p_message;
--|| p_content;
--dbms_output.put_line(msg);
mail_conn := UTL_SMTP.open_connection (v_mailhost, 25);
UTL_SMTP.helo (mail_conn, v_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 (p_user)
)
)
);
UTL_SMTP.command (mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass)
)
)
);
UTL_SMTP.mail (mail_conn, p_sender);
UTL_SMTP.rcpt (mail_conn, p_recipient);
UTL_SMTP.DATA (mail_conn, msg);
UTL_SMTP.quit (mail_conn);
--EXCEPTION WHEN OTHERS
-- THEN
-- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
-- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
NULL;
END send_mail1;
/
我执行会报如下错误,请问如何解决???先谢了
SQL> exec send_mail2
BEGIN send_mail2; END;*
ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 501 Bad address syntax
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在"DSTRANS.SEND_MAIL2", line 50
ORA-06512: 在line 1
CREATE OR REPLACE PROCEDURE send_mail (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2 DEFAULT 'Oracle Perf Report '
|| TO_CHAR (SYSDATE,
'yyyy-mm-dd hh24:mi:ss'
),
p_sender IN VARCHAR2 DEFAULT '[email protected]'
)
IS
v_mailhost VARCHAR2 (30) := 'mail.longshine.com';--SMTP服务器地址
mail_conn UTL_SMTP.connection;
msg VARCHAR2 (4000);
p_user VARCHAR2 (30) := '[email protected]';
--登录SMTP服务器的用户名
p_pass VARCHAR2 (30) := 'test'; --登录SMTP服务器的密码
BEGIN
/* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
msg :=
'Date:'
|| TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')
|| UTL_TCP.crlf
|| 'From: '
|| p_sender
|| '<'
|| p_sender
|| '>'
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| '<'
|| p_recipient
|| '>'
|| UTL_TCP.crlf
|| 'Subject: '
|| p_subject
|| UTL_TCP.crlf
|| UTL_TCP.crlf
|| p_message;
-- dbms_output.put_line(msg);
mail_conn := UTL_SMTP.open_connection (v_mailhost, 25);
UTL_SMTP.helo (mail_conn, v_mailhost);
/* smtp服务器登录校验 */
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 (p_user)
)
)
);
UTL_SMTP.command
(mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass)
)
)
);
UTL_SMTP.mail (mail_conn, p_sender);
UTL_SMTP.rcpt (mail_conn, p_recipient);
/* 发送数据 */
UTL_SMTP.DATA (mail_conn, msg);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
NULL;
END send_mail;