[Developer] Oracle send mail procedure(2)
CREATE OR REPLACE PACKAGE demo_base64 IS
FUNCTION encode(r IN RAW) RETURN VARCHAR2;
end;
CREATE OR REPLACE
PACKAGE BODY demo_base64 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
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;
=============================================================================
CREATE OR REPLACE PROCEDURE sendmail(p_body in long, p_subject in varchar2)
is
p_sender varchar2(30) := 'automail@wlcsp.com';
-- p_subject varchar2(50) := 'Test';
mail_conn utl_smtp.connection;
mail_host varchar2(100) := 'smtp.wlcsp.com';
user_name varchar2(156) := 'automail@wlcsp.com';
user_pwd varchar2(156) := '1234qwer.';
M_EMAIL VARCHAR2(100);
cursor CUR_Email is select email from e_mail_address order by email;
begin
mail_conn := utl_smtp.open_connection(mail_host, 25);
utl_smtp.ehlo(mail_conn, mail_host);
utl_smtp.command(mail_conn, 'AUTH LOGIN');
utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_name)));
utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_pwd)));
--UTL_SMTP.command(mail_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(user_name))));
--UTL_SMTP.command(mail_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(user_pwd))));
utl_smtp.mail(mail_conn, '<' || p_sender || '>');
OPEN cur_Email;
LOOP
FETCH CUR_Email INTO M_EMAIL;
EXIT WHEN CUR_Email%NOTFOUND;
utl_smtp.rcpt(mail_conn, '<' || M_EMAIL || '>');
END LOOP;
CLOSE cur_Email;
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'Date:'|| TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss') || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'From:' || p_sender ||'<' || p_sender || '>' || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'To:' || M_EMAIL ||'<' || M_EMAIL || '>' || utl_tcp.crlf);
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Subject:' || p_subject || utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GBK' || utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK')));
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
when others then
raise_application_error(-20001, 'The send mail was error ' || sqlerrm);
end sendmail;
FUNCTION encode(r IN RAW) RETURN VARCHAR2;
end;
CREATE OR REPLACE
PACKAGE BODY demo_base64 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
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;
=============================================================================
CREATE OR REPLACE PROCEDURE sendmail(p_body in long, p_subject in varchar2)
is
p_sender varchar2(30) := 'automail@wlcsp.com';
-- p_subject varchar2(50) := 'Test';
mail_conn utl_smtp.connection;
mail_host varchar2(100) := 'smtp.wlcsp.com';
user_name varchar2(156) := 'automail@wlcsp.com';
user_pwd varchar2(156) := '1234qwer.';
M_EMAIL VARCHAR2(100);
cursor CUR_Email is select email from e_mail_address order by email;
begin
mail_conn := utl_smtp.open_connection(mail_host, 25);
utl_smtp.ehlo(mail_conn, mail_host);
utl_smtp.command(mail_conn, 'AUTH LOGIN');
utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_name)));
utl_smtp.command(mail_conn, demo_base64.encode(utl_raw.cast_to_raw(user_pwd)));
--UTL_SMTP.command(mail_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(user_name))));
--UTL_SMTP.command(mail_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(user_pwd))));
utl_smtp.mail(mail_conn, '<' || p_sender || '>');
OPEN cur_Email;
LOOP
FETCH CUR_Email INTO M_EMAIL;
EXIT WHEN CUR_Email%NOTFOUND;
utl_smtp.rcpt(mail_conn, '<' || M_EMAIL || '>');
END LOOP;
CLOSE cur_Email;
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'Date:'|| TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss') || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'From:' || p_sender ||'<' || p_sender || '>' || utl_tcp.CRLF);
utl_smtp.write_data(mail_conn, 'To:' || M_EMAIL ||'<' || M_EMAIL || '>' || utl_tcp.crlf);
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Subject:' || p_subject || utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert('Content-Type:text/html;charset=GBK' || utl_tcp.CRLF, 'ZHS16GBK')));
utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(convert(p_body, 'ZHS16GBK')));
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
when others then
raise_application_error(-20001, 'The send mail was error ' || sqlerrm);
end sendmail;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2074748/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle send mailOracleAI
- [Developer] Procedure傳送html形式表格的mailDeveloperHTMLAI
- Oracle stored procedure to send emailOracleAI
- Send MailAI
- [Developer] Oracle sendmail procedure(1)(轉載)DeveloperOracleAI
- django send_mail功能DjangoAI
- [原創]How to send patch files by git send-mailGitAI
- mail can't send title on SolarisAI
- C# send mail with outlook and word mailmergeC#AI
- Send E-mail with PDF attachment using Node.jsAINode.js
- 在SQL Developer中Debug一個procedureSQLDeveloper
- Oracle Wrap ProcedureOracle
- Others_2_MailAI
- 資料庫包send_mail發郵件的配置含ACL和privilege資料庫AI
- 【Oracle】-PX Deq Credit: send blkdOracle
- How to rename an Oracle stored procedureOracle
- oracle 中呼叫 store procedureOracle
- oracle mail utl_smtpOracleAI
- Oracle Developer 論壇OracleDeveloper
- 呼叫sp_send_mail時出現Msg 22050和Msg 14661錯誤AI
- Building a Dynamic Oracle ETL ProcedureUIOracle
- Oracle之procedure的基礎使用Oracle
- DB2 PL/SQL Example: Sleep ProcedureDB2SQL
- Oracle Display the process of the procedure ( oracle pipe , dbms_output , prompt )Oracle
- oracle sql developer 2.1如何連線oracleOracleSQLDeveloper
- PL/SQL Developer 連線 OracleSQLDeveloperOracle
- Oracle SQL Developer安裝教程OracleSQLDeveloper
- ORACLE SQL DEVELOPER User Interface ChangeOracleSQLDeveloper
- [Procedure]Oracle之分頁儲存過程Oracle儲存過程
- 用Oracle傳送郵件procedure (zt)Oracle
- Oracle 基礎 ----procedure(儲存過程)Oracle儲存過程
- os ,shutil,send2trash模組彙總(tcy)
- XMLHttpRequest send()XMLHTTP
- 呼叫遠端procedure的2個問題
- 【Oracle】並行等待之PX Deq Credit: send blkdOracle並行
- ORACLE SQL Developer 64 bit DownloadOracleSQLDeveloper
- 【Oracle】Windows安裝pl/sql developerOracleWindowsSQLDeveloper
- oracle儲存過程procedure_輸入引數varchar2與char報錯Oracle儲存過程