请问各位大侠谁有在oracle 11g中执行发邮件的存储过程

我有,保证可以用...
如下:CREATE OR REPLACE PROCEDURE send_mail(
     p_recipient VARCHAR2, -- 邮件接收人
     p_subject   VARCHAR2, -- 邮件标题
     p_message   VARCHAR2  -- 邮件正文
)
IS  --下面四个变量请根据实际邮件服务器进行赋值
v_mailhost  VARCHAR2(30) := 'smtp.sina.com';     --SMTP服务器地址
   v_user      VARCHAR2(30) := 'ylp9888'; --登录SMTP服务器的用户名；只是用户名，不包括163.com部分
   v_pass      VARCHAR2(20) := '你的密码';  --登录SMTP服务器的密码
   v_sender    VARCHAR2(50)  := '[email protected]'; --发送都邮箱，一般与 ps_user 对应   v_conn  UTL_SMTP.connection; --到邮件服务器的连接
   v_msg varchar2(4000);  --邮件内容 BEGIN v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
UTL_SMTP.ehlo(v_conn, v_mailhost); --是用 ehlo() 而不是 helo() 函数
        --否则会报：ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first. UTL_SMTP.command(v_conn, 'AUTH LOGIN');   -- smtp服务器登录校验
UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
      UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));      UTL_SMTP.mail(v_conn, '<' || v_sender || '>');     --设置发件人
      UTL_SMTP.rcpt(v_conn, '<' || p_recipient || '>');  --设置收件人      -- 创建要发送的邮件内容注意报头信息和邮件正文之间要空一行
      v_msg :='Date:'|| TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss')
         || UTL_TCP.CRLF || 'From: '|| v_sender || '<' || v_sender || '>'
        || UTL_TCP.CRLF || 'To: '  || p_recipient || '<' || p_recipient || '>'
         || UTL_TCP.CRLF || 'Subject: ' || p_subject
         || UTL_TCP.CRLF || UTL_TCP.CRLF  -- 这前面是报头信息
         || p_message;    -- 这个是邮件正文      UTL_SMTP.open_data(v_conn); --打开流
      UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg)); --这样写标题和内容都能用中文
      UTL_SMTP.close_data(v_conn); --关闭流
      UTL_SMTP.quit(v_conn); --关闭连接 EXCEPTION    WHEN OTHERS THEN
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);END send_mail;

谢谢你，我的是11g，我以前的存储过程跟你的差不多，在测试你的这个存储过程时和以前我的一样报如下的错：ORA-24247: 网络访问被网络访问列表 (ACL) 拒绝----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
229BE4BC        47  procedure IPCC.SEND_MAIL_SECEND
229ADD98         3  anonymous block
还在找原因呢！

ORA-24247 的错误我已经查到：
ORA-24247: network access denied by access control list (ACL)
Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.
Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.不知道怎么改这个问题？？

http://space.itpub.net/763776/viewspace-617139原因：11g数据库为了安全起见，默认disable一些utility，用的话需要授权：你试试...

下面这我是在用的邮件发送存储过程,你可以参考一下.我也是参考了别人的.希望对你有用
邮件发送CREATE OR REPLACE PROCEDURE SEND_MAIL
        (as_sender in varchar2, --邮件发送者
        as_recp in varchar2, --邮件接收者
        as_subject in varchar2, --邮件标题
        as_msg_body in varchar2) --邮件内容
        IS
        ls_mailhost varchar2(30) := mail.163.com; -- address or IP
        lc_mail_conn utl_smtp.connection;
        ls_subject varchar2(100);
        ls_msg_body varchar2(20000);
        ls_username varchar2(256) := 'yourname';
        ls_password varchar2(256) := '******';
        BEGIN
        lc_mail_conn := utl_smtp.open_connection(ls_mailhost, 25);
        utl_smtp.helo(lc_mail_conn, ls_mailhost);
        utl_smtp.command(lc_mail_conn, 'AUTH LOGIN');
        utl_smtp.command(lc_mail_conn, mailchar_encode.encode(utl_raw.cast_to_raw(ls_username)));
        utl_smtp.command(lc_mail_conn, mailchar_encode.encode(utl_raw.cast_to_raw(ls_password)));
        ls_subject := 'Subject: [' || upper(sys_context('userenv', 'db_name')) || '] - ' || as_subject;
        ls_msg_body := as_msg_body;
        utl_smtp.mail(lc_mail_conn, '<'||as_sender||'>'); --这里的'<' 一定要写,不然会出现permanent error
        utl_smtp.rcpt(lc_mail_conn, '<'||as_recp||'>');--这里的'<' 一定要写,不然会出现permanent error
        utl_smtp.open_data(lc_mail_conn);
        ls_msg_body := 'From: ' || as_sender || chr(13) || chr(10) || 'To: ' || as_recp || chr(13) || chr(10) || ls_subject ||
        chr(13) || chr(10) || chr(13) || chr(10) || ls_msg_body;
        utl_smtp.write_raw_data(lc_mail_conn, utl_raw.cast_to_raw(ls_msg_body)); --这样写subject可以支持中文但body内容不支持中文;
        -- utl_smtp.write_data(lc_mail_conn, ls_msg_body); --这样写subject不支持中文
        utl_smtp.close_data(lc_mail_conn);
        utl_smtp.quit(lc_mail_conn);
        EXCEPTION
        WHEN UTL_SMTP.INVALID_OPERATION THEN
        dbms_output.put_line('invalid operation');
        WHEN UTL_SMTP.TRANSIENT_ERROR THEN
        dbms_output.put_line('transient error');
        dbms_output.put_line('sqlcode is :'||sqlcode);
        dbms_output.put_line('sqlerrm is :'||sqlerrm);
        WHEN UTL_SMTP.PERMANENT_ERROR THEN
        dbms_output.put_line('permanent error');
        WHEN OTHERS THEN
        dbms_output.put_line('others');
        end send_mail;
/主要用于字符解码
创建包头
create or replace package mailchar_encode is  -- 主要用来解码的函数
  FUNCTION encode(r IN RAW) RETURN VARCHAR2;end ;
/穿件包体
CREATE OR REPLACE PACKAGE BODY mailchar_encode IS
        TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
        map vc2_table;
        PROCEDURE init_map IS
        BEGIN
        map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
        map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
        map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
        map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
        map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
        map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
        map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
        map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
        map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
        map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
        map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
        map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
        map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
        END;
        FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS
        i pls_integer;
        x pls_integer;
        y pls_integer;
        v VARCHAR2(32767);
        BEGIN
        -- For every 3 bytes, split them into 4 6-bit units and map them to
        -- the characters
        i := 1;
        WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
        x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
        to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
        to_number(utl_raw.substr(r, i + 2, 1), '0X');
        y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
        y := floor(x / 4096); v := v || map(y); x := x - y * 4096;
        y := floor(x / 64); v := v || map(y); x := x - y * 64;
        v := v || map(x);
        i := i + 3;
        END LOOP;
        -- Process the remaining bytes that has fewer than 3 bytes.
        IF ( utl_raw.length(r) - i = 0) THEN
        x := to_number(utl_raw.substr(r, i, 1), '0X');
        y := floor(x / 4); v := v || map(y); x := x - y * 4;
        x := x * 16; v := v || map(x);
        v := v || '==';
        ELSIF ( utl_raw.length(r) - i = 1) THEN
        x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
        to_number(utl_raw.substr(r, i + 1, 1), '0X');
        y := floor(x / 1024); v := v || map(y); x := x - y * 1024;
        y := floor(x / 16); v := v || map(y); x := x - y * 16;
        x := x * 4; v := v || map(x);
        v := v || '=';
        END IF;
        RETURN v;
        END;
        BEGIN
        init_map;
        END;
/

ACL 的问题已经解决；现在在执行     UTL_SMTP.command(v_conn,'AUTH LOGIN') 时抛出如下异常：
ORA-29279: SMTP 永久性错误: 502 Command "AUTH LOGIN" not implemented
ORA-29279: SMTP 永久性错误: 502 Command "AUTH LOGIN" not implemented----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
251A3FB8        55  procedure IPCC.SEND_MAIL
22BAB91C         3  anonymous block
哪位大侠有遇到此类问题？谢谢指点！！！

怎么解决的，我原先也偶到过这个问题，所以放弃了ORACLE直接发邮件。告诉我一下，学习ING

哦，我想起来我当初为什么放弃ORACLE直接发邮件的另一个原因了——客户那里ORACLE的服务器不允许直接上网

ACL 的问题已经解决；现在在执行   UTL_SMTP.command(v_conn,'AUTH LOGIN') 时抛出如下异常：
ORA-29279: SMTP 永久性错误: 502 Command "AUTH LOGIN" not implemented
ORA-29279: SMTP 永久性错误: 502 Command "AUTH LOGIN" not implemented----- PL/SQL Call Stack -----
  object line object
  handle number name
251A3FB8 55 procedure IPCC.SEND_MAIL
22BAB91C 3 anonymous block我目前也遇到这样的问题，请问小妹，怎样解决？先谢啦！

调试易

请问各位大侠谁有在oracle 11g中执行发邮件的存储过程

解决方案 »