CHAR2,
p_splite VARCHAR2 DEFAULT ':',
p_crlf VARCHAR2 DEFAULT l_crlf)
IS
BEGIN
/* utl_raw.cast_to_raw to handle chinese code*/
UTL_SMTP.write_raw_data (
p_conn,
UTL_RAW.cast_to_raw (
CONVERT (p_name || p_splite || p_value || p_crlf,
'ZHS16CGB231280')));
END;
----------------------------------------write mime mail tail-----------------------------------------------------
PROCEDURE end_boundary (conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT FALSE)
IS
BEGIN
UTL_SMTP.write_data (conn, UTL_TCP.crlf);
IF (LAST)
THEN
UTL_SMTP.write_data (conn, last_boundary);
END IF;
END;
---------------------------------------------send mail procedure--------------------------------------------
PROCEDURE p_email (p_sendoraddress2 VARCHAR2, --sender address
p_receiveraddress2 VARCHAR2) --reciever address
IS
l_conn UTL_SMTP.connection; --create a connection
BEGIN
/*Initial mail server*/
l_conn := UTL_SMTP.open_connection (p_server, p_port);
UTL_SMTP.helo (l_conn, p_server);
/* smtp authentication*/
IF p_need_smtp = 1
THEN
UTL_SMTP.command (l_conn, 'AUTH LOGIN', '');
UTL_SMTP.command (
l_conn,
UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_user))));
UTL_SMTP.command (
l_conn,
UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass))));
END IF;
/*configure sender and reciever mail address*/
UTL_SMTP.mail (l_conn, p_sendoraddress2);
UTL_SMTP.rcpt (l_conn, p_receiveraddress2);
/*configure mail header*/
UTL_SMTP.open_data (l_conn);
/*configure date*/
--write_data(l_conn, 'Date', to_char(sysdate-1/3, 'dd Mon yy hh24:mi:ss'));
/*configure sender*/
write_data (l_conn, 'From', p_sendor);
/*configure reciever*/
write_data (l_conn, 'To', p_receiver);
/*add mail subject*/
SELECT REPLACE (
'= GB2312 B '
|| UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (RAWTOHEX (p_sub)))
|| ' =',
UTL_TCP.crlf,
'')
INTO p_subject
FROM DUAL;
write_data (l_conn, 'Subject', p_subject);
write_data (l_conn, 'Content-Type', multipart_mime_type);
UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
UTL_SMTP.write_data (l_conn, first_boundary);
write_data (l_conn, 'Content-Type', 'text/html');
UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
write_data (
l_conn,
'',
REPLACE (REPLACE (p_txt, l_splite, CHR (10)), CHR (10), l_crlf),
'',
'');
end_boundary (l_conn);
/*close write data*/
UTL_SMTP.close_data (l_conn);
/*close connection*/
UTL_SMTP.quit (l_conn);
END;
---------------------------------------------main procedure -----------------------------------------------------
BEGIN
err_num := 0;
l_sendoraddress := '<' || p_sendor || '>';
p_splite_str (p_receiver); --handle mail address
FOR k IN 1 .. my_address_list.COUNT
LOOP
p_email (l_sendoraddress, my_address_list (k));
END LOOP;
END;
END;
/
更多参考
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
使用PL/SQL Developer剖析PL/SQL代码
对比 PL/SQL profiler 剖析结果
PL/SQL Profiler 剖析报告生成html
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的