分享
 
 
 

如何在Oracle中发送Email

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

在Oracle中发送email,以下是Oracle提供的例子,供参考.

itpub link:

http://www.itpub.net/showthread.PHP?threadid=226086

1. CREATE PACKAGE

CREATE OR REPLACE PACKAGE demo_mail IS

----------------------- Customizable Section -----------------------

-- Customize the SMTP host, port and your domain name below.

smtp_host VARCHAR2(256) := 'smtp.eygle.com';

smtp_port PLS_INTEGER := 25;

smtp_domain VARCHAR2(256) := 'eygle.com';

-- Customize the signature that will appear in the email's MIME header.

-- Useful for versioning.

MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Eygle';

--------------------- End Customizable Section ---------------------

-- A unique string that demarcates boundaries of parts in a multi-part email

-- The string should not appear inside the body of any part of the email.

-- Customize this if needed or generate this randomly dynamically.

BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' BOUNDARY utl_tcp.CRLF;

LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' BOUNDARY '--'

utl_tcp.CRLF;

-- A MIME type that denotes multi-part email (MIME) messages.

MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'

BOUNDARY '"';

MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;

-- A simple email API for sending email in plain text in a single call.

-- The format of an email address is one of these:

-- someone@some-domain

-- "Someone at some domain" <someone@some-domain>

-- Someone at some domain <someone@some-domain>

-- The recipients is a list of email addresses separated by

-- either a "," or a ";"

PROCEDURE mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

message IN VARCHAR2);

-- Extended email API to send email in Html or plain text with no size limit.

-- First, begin the email by begin_mail(). Then, call write_text() repeatedly

-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send

-- email in non-ASCII or multi-byte character set. End the email with

-- end_mail().

FUNCTION begin_mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL)

RETURN utl_smtp.connection;

-- Write email body in ASCII

PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2);

-- Write email body in non-ASCII (including multi-byte). The email body

-- will be sent in the database character set.

PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2);

-- Write email body in binary

PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,

message IN RAW);

-- APIs to send email with attachments. Attachments are sent by sending

-- emails in "multipart/mixed" MIME format. Specify that MIME format when

-- beginning an email with begin_mail().

-- Send a single text attachment.

PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,

data IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE);

-- Send a binary attachment. The attachment will be encoded in Base-64

-- encoding format.

PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,

data IN RAW,

mime_type IN VARCHAR2 DEFAULT 'application/octet',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE);

-- Send an attachment with no size limit. First, begin the attachment

-- with begin_attachment(). Then, call write_text repeatedly to send

-- the attachment piece-by-piece. If the attachment is text-based but

-- in non-ASCII or multi-byte character set, use write_mb_text() instead.

-- To send binary attachment, the binary content should first be

-- encoded in Base-64 encoding format using the demo package for 8i,

-- or the native one in 9i. End the attachment with end_attachment.

PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

transfer_enc IN VARCHAR2 DEFAULT NULL);

-- End the attachment.

PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,

last IN BOOLEAN DEFAULT FALSE);

-- End the email.

PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

-- Extended email API to send multiple emails in a session for better

-- performance. First, begin an email session with begin_session.

-- Then, begin each email with a session by calling begin_mail_in_session

-- instead of begin_mail. End the email with end_mail_in_session instead

-- of end_mail. End the email session by end_session.

FUNCTION begin_session RETURN utl_smtp.connection;

-- Begin an email in a session.

PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,

sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL);

-- End an email in a session.

PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);

-- End an email session.

PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;

/

CREATE OR REPLACE PACKAGE BODY demo_mail IS

-- Return the next email address in the list of email addresses, separated

-- by either a "," or a ";". The format of mailbox may be in one of these:

-- someone@some-domain

-- "Someone at some domain" <someone@some-domain>

-- Someone at some domain <someone@some-domain>

FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

addr VARCHAR2(256);

i pls_integer;

FUNCTION lookup_unquoted_char(str IN VARCHAR2,

chrs IN VARCHAR2) RETURN pls_integer AS

c VARCHAR2(5);

i pls_integer;

len pls_integer;

inside_quote BOOLEAN;

BEGIN

inside_quote := false;

i := 1;

len := length(str);

WHILE (i <= len) LOOP

c := substr(str, i, 1);

IF (inside_quote) THEN

IF (c = '"') THEN

inside_quote := false;

ELSIF (c = '\') THEN

i := i + 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(chrs, c) >= 1) THEN

RETURN i;

END IF;

<<next_char>>

i := i + 1;

END LOOP;

RETURN 0;

END;

BEGIN

addr_list := ltrim(addr_list);

i := lookup_unquoted_char(addr_list, ',;');

IF (i >= 1) THEN

addr := substr(addr_list, 1, i - 1);

addr_list := substr(addr_list, i + 1);

ELSE

addr := addr_list;

addr_list := '';

END IF;

i := lookup_unquoted_char(addr, '<');

IF (i >= 1) THEN

addr := substr(addr, i + 1);

i := instr(addr, '>');

IF (i >= 1) THEN

addr := substr(addr, 1, i - 1);

END IF;

END IF;

RETURN addr;

END;

-- Write a MIME header

PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,

name IN VARCHAR2,

value IN VARCHAR2) IS

BEGIN

utl_smtp.write_data(conn, name ': ' value utl_tcp.CRLF);

END;

-- Mark a message-part boundary. Set <last> to TRUE for the last boundary.

PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,

last IN BOOLEAN DEFAULT FALSE) AS

BEGIN

IF (last) THEN

utl_smtp.write_data(conn, LAST_BOUNDARY);

ELSE

utl_smtp.write_data(conn, FIRST_BOUNDARY);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

message IN VARCHAR2) IS

conn utl_smtp.connection;

BEGIN

conn := begin_mail(sender, recipients, subject);

write_text(conn, message);

end_mail(conn);

END;

------------------------------------------------------------------------

FUNCTION begin_mail(sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL)

RETURN utl_smtp.connection IS

conn utl_smtp.connection;

BEGIN

conn := begin_session;

begin_mail_in_session(conn, sender, recipients, subject, mime_type,

priority);

RETURN conn;

END;

------------------------------------------------------------------------

PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2) IS

BEGIN

utl_smtp.write_data(conn, message);

END;

------------------------------------------------------------------------

PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,

message IN VARCHAR2) IS

BEGIN

utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));

END;

------------------------------------------------------------------------

PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,

message IN RAW) IS

BEGIN

utl_smtp.write_raw_data(conn, message);

END;

------------------------------------------------------------------------

PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,

data IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE) IS

BEGIN

begin_attachment(conn, mime_type, inline, filename);

write_text(conn, data);

end_attachment(conn, last);

END;

------------------------------------------------------------------------

PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,

data IN RAW,

mime_type IN VARCHAR2 DEFAULT 'application/octet',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

last IN BOOLEAN DEFAULT FALSE) IS

i PLS_INTEGER;

len PLS_INTEGER;

BEGIN

begin_attachment(conn, mime_type, inline, filename, 'base64');

-- Split the Base64-encoded attachment into multiple lines

i := 1;

len := utl_raw.length(data);

WHILE (i < len) LOOP

IF (i + MAX_BASE64_LINE_WIDTH < len) THEN

utl_smtp.write_raw_data(conn,

utl_encode.base64_encode(utl_raw.substr(data, i,

MAX_BASE64_LINE_WIDTH)));

ELSE

utl_smtp.write_raw_data(conn,

utl_encode.base64_encode(utl_raw.substr(data, i)));

END IF;

utl_smtp.write_data(conn, utl_tcp.CRLF);

i := i + MAX_BASE64_LINE_WIDTH;

END LOOP;

end_attachment(conn, last);

END;

------------------------------------------------------------------------

PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

inline IN BOOLEAN DEFAULT TRUE,

filename IN VARCHAR2 DEFAULT NULL,

transfer_enc IN VARCHAR2 DEFAULT NULL) IS

BEGIN

write_boundary(conn);

write_mime_header(conn, 'Content-Type', mime_type);

IF (filename IS NOT NULL) THEN

IF (inline) THEN

write_mime_header(conn, 'Content-Disposition',

'inline; filename="'filename'"');

ELSE

write_mime_header(conn, 'Content-Disposition',

'attachment; filename="'filename'"');

END IF;

END IF;

IF (transfer_enc IS NOT NULL) THEN

write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);

END IF;

utl_smtp.write_data(conn, utl_tcp.CRLF);

END;

------------------------------------------------------------------------

PROCEDURE end_attachment(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

write_boundary(conn, last);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

end_mail_in_session(conn);

end_session(conn);

END;

------------------------------------------------------------------------

FUNCTION begin_session RETURN utl_smtp.connection IS

conn utl_smtp.connection;

BEGIN

-- open SMTP connection

conn := utl_smtp.open_connection(smtp_host, smtp_port);

utl_smtp.helo(conn, smtp_domain);

RETURN conn;

END;

------------------------------------------------------------------------

PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,

sender IN VARCHAR2,

recipients IN VARCHAR2,

subject IN VARCHAR2,

mime_type IN VARCHAR2 DEFAULT 'text/plain',

priority IN PLS_INTEGER DEFAULT NULL) IS

my_recipients VARCHAR2(32767) := recipients;

my_sender VARCHAR2(32767) := sender;

BEGIN

-- Specify sender's address (our server allows bogus address

-- as long as it is a full email address (xxx@yyy.com).

utl_smtp.mail(conn, get_address(my_sender));

-- Specify recipient(s) of the email.

WHILE (my_recipients IS NOT NULL) LOOP

utl_smtp.rcpt(conn, get_address(my_recipients));

END LOOP;

-- Start body of email

utl_smtp.open_data(conn);

-- Set "From" MIME header

write_mime_header(conn, 'From', sender);

-- Set "To" MIME header

write_mime_header(conn, 'To', recipients);

-- Set "Subject" MIME header

write_mime_header(conn, 'Subject', subject);

-- Set "Content-Type" MIME header

write_mime_header(conn, 'Content-Type', mime_type);

-- Set "X-Mailer" MIME header

write_mime_header(conn, 'X-Mailer', MAILER_ID);

-- Set priority:

-- High Normal Low

-- 1 2 3 4 5

IF (priority IS NOT NULL) THEN

write_mime_header(conn, 'X-Priority', priority);

END IF;

-- Send an empty line to denotes end of MIME headers and

-- beginning of message body.

utl_smtp.write_data(conn, utl_tcp.CRLF);

IF (mime_type LIKE 'multipart/mixed%') THEN

write_text(conn, 'This is a multi-part message in MIME format.'

utl_tcp.crlf);

END IF;

END;

------------------------------------------------------------------------

PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

utl_smtp.close_data(conn);

END;

------------------------------------------------------------------------

PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS

BEGIN

utl_smtp.quit(conn);

END;

END;

/

2.发送 MAIL:

CREATE OR REPLACE procedure MailTest IS

Begin

DEMO_MAIL.mail('eygle@itpub.net',

'gqgai@hurray.com.cn',

'Just a test',

'It is ok.');

end;

/

begin

MailTest;

end;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有