好像这个包是9i以后才有的。
对于8i,metalink提供的有另外的实现方法
对于8i,metalink提供的有另外的实现方法
解决方案 »
- 32位oracle11g 安装在64位的server2008下需要做什么更改?
- oracle类似这样的统计函数怎么写?
- 关于exists和内连接
- 在ORACLE中能否发送HTTP请求
- fc7 安装oracle9i(920) 出错 ---在线
- root连接oracle 提示:ORA-12546: TNS:permission denied (AIX环境)
- liunx系统里面从数据库查询大量数据时报如下错,求解
- 学习oracle,看什么书好?
- 请教: 我的oracle9i装完了, 可是系统盘大了5个G, 已经没有空间用了. 不解!!
- 关于Oracle查询结果格式转换的问题
- 求救!刚学ORACLE9i所遇到的问题!!!
- 请问如何访问xp sp2上安装的oracle数据库?
p_recipient IN VARCHAR2,
p_sender IN VARCHAR2,
p_pass IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2
)
IS
v_mailhost varchar2(30); -- SMTP服务器地址
mail_conn utl_smtp.connection;
msg varchar2(4000);
p_user varchar2(30); -- 登录SMTP服务器的用户名
BEGIN
select substr(p_sender,instr(p_sender,'@'),100) INTO p_user from dual;
select substr(p_sender,1,instr(p_sender,'@')-1 ) INTO p_user from dual; /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
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);
END send_mail;
p_recipient IN VARCHAR2,
p_sender IN VARCHAR2,
p_pass IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2
)
IS
v_mailhost varchar2(30); -- SMTP服务器地址
mail_conn utl_smtp.connection;
msg varchar2(4000);
p_user varchar2(30); -- 登录SMTP服务器的用户名
BEGIN
select substr(p_sender,instr(p_sender,'@')+1,100) INTO v_mailhost from dual;
select substr(p_sender,1,instr(p_sender,'@')-1 ) INTO p_user from dual; /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
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);
END send_mail;
IS
TYPE map_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
map map_table;
-- Initialize the Base64 mapping
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 Base64 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;
PROCEDURE send_mail( i_to VARCHAR2, i_cc in VARCHAR2, i_bcc VARCHAR2, i_subject VARCHAR2, i_content VARCHAR2,i_from VARCHAR2,i_pwd VARCHAR2)
is
c utl_smtp.connection;
v_tmp varchar2(256);
v_adr varchar2(256);
v_from varchar2(256);
v_to varchar2(256);
v_cc varchar2(256);
v_bcc varchar2(256);
i_port NUMBER:= 25;
i_smtp_server VARCHAR2(100);
i_user VARCHAR2(100);
-- encodeBase64
function EncodeBase64(i_str varchar2) return varchar2 is
begin
return encode( utl_raw.cast_to_raw(i_str) );
end;
-- EncodeAddress
function EncodeAddress(i_str varchar2) return varchar2 is
begin
return ('=?GB2312?B?'||EncodeBase64(i_str)||'?=');
end;
-- getAddress
function getAddress(x_addr in out varchar2, x_addr_encoded in out varchar2)
return varchar2 is
v_addr varchar2(256);
v_addr_encoded varchar2(256);
v_separator varchar2(5);
i pls_integer;
function lookup_unquoted_char(li_str varchar2, li_chrs varchar2) return pls_integer as
c varchar2(5);
k pls_integer;
l pls_integer;
inside_quote boolean;
begin
inside_quote := false;
k := 1;
l := length(li_str);
while (k <= l)
loop
c := substr(li_str, k, 1);
if (inside_quote) then
if (c = '"') then
inside_quote := false;
elsif (c = '\') then
k := k + 1; -- skip the quote character
end if;
goto next_char;
end if;
if (c = '"') then
inside_quote := true;
goto next_char;
end if;
if (instr(li_chrs, c) >= 1) then
return k;
end if;
<<next_char>>
k := k + 1;
end loop;
return 0;
end;
begin
x_addr := ltrim(x_addr);
i := lookup_unquoted_char(x_addr, ',;');
if (i >= 1) then
v_separator := substr(x_addr, i, 1);
v_addr := substr(x_addr, 1, i - 1);
x_addr := substr(x_addr, i + 1);
else
v_addr := x_addr;
x_addr := '';
end if;
i := lookup_unquoted_char(v_addr, '<');
if (i >= 1) then
v_addr_encoded := EncodeAddress(substr(v_addr, 1, i - 1)) || substr(v_addr, i);
v_addr := substr(v_addr, i + 1);
i := instr(v_addr, '>');
if (i >= 1) then
v_addr := substr(v_addr, 1, i - 1);
end if;
else
v_addr_encoded := v_addr;
end if;
x_addr_encoded := x_addr_encoded || v_addr_encoded || v_separator;
return v_addr;
end getAddress;
BEGIN
select substr(i_from,instr(i_from,'@')+1,50) INTO i_smtp_server from dual;
select substr(i_from,1,instr(i_from,'@')-1 ) INTO i_user from dual;
c := utl_smtp.open_connection(i_smtp_server, i_port);
utl_smtp.helo(c, i_smtp_server); -- 校验认证
if i_user is not null then
utl_smtp.command(c, 'auth login', '');
utl_smtp.command(c, EncodeBase64(i_user), '');
utl_smtp.command(c, EncodeBase64(i_pwd), '');
end if; -- 解析 发信人
v_tmp := i_from;
v_adr := getAddress(v_tmp, v_from);
utl_smtp.mail(c, v_adr); -- 解析 收件人列表
v_tmp := i_to;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_to);
utl_smtp.rcpt(c, v_adr);
end loop;
-- 解析 抄送人列表
v_tmp := i_cc;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_cc);
utl_smtp.rcpt(c, v_adr);
end loop;
-- 解析 暗送人列表
v_tmp := i_bcc;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_bcc);
utl_smtp.rcpt(c, v_adr);
end loop; utl_smtp.open_data(c);
utl_smtp.write_data(c, 'From: '||v_from||utl_tcp.CRLF);
utl_smtp.write_data(c, 'To: '||v_to||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Cc: '||v_cc||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Subject: '||EncodeAddress(i_subject)||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Mime-Version: 1.0'||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Content-Type: text/plain; charset="GB2312"'||utl_tcp.CRLF);
utl_smtp.write_data(c, utl_tcp.CRLF); -- data
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(i_content));
utl_smtp.write_data(c, utl_tcp.CRLF); utl_smtp.close_data(c);
utl_smtp.quit(c); EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(c);
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END send_mail;
BEGIN
init_map;
END;
/