www.eygle.com

SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);

SQL>execute statspack.snap(i_snap_level=>10);

SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000;

file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y

exp userid=perfstat/my_perfstat_password parfile=spuexp.par

SQL> select max(snap_id) from stats$snapshot;

MAX(SNAP_ID)

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

166

SQL> delete from stats$snapshot where snap_id < = 166;

143 rows deleted

SQL> select a.object_id,a.oracle_username ,b.object_name
from v$locked_object a,dba_objects b
where a.object_id = b.object_id
/

OBJECT_ID ORACLE_USERNAMEOBJECT_NAME

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

156 PERFSTATSNAP$

39700 PERFSTATSTATS$LIBRARYCACHE

39706 PERFSTATSTATS$ROLLSTAT

39712 PERFSTATSTATS$SGA

39754 PERFSTATSTATS$PARAMETER

39745 PERFSTATSTATS$SQL_STATISTICS

39739 PERFSTATSTATS$SQL_SUMMARY

39736 PERFSTATSTATS$ENQUEUESTAT

39733 PERFSTATSTATS$WAITSTAT

39730 PERFSTATSTATS$BG_EVENT_SUMMARY

39724 PERFSTATSTATS$SYSTEM_EVENT

39718 PERFSTATSTATS$SYSSTAT

39715 PERFSTATSTATS$SGASTAT

39709 PERFSTATSTATS$ROWCACHE_SUMMARY

39703 PERFSTATSTATS$BUFFER_POOL_STATISTICS

39697 PERFSTATSTATS$LATCH_MISSES_SUMMARY

39679 PERFSTATSTATS$SNAPSHOT

39682 PERFSTATSTATS$FILESTATXS

39688 PERFSTATSTATS$LATCH

174 PERFSTATJOB$

20 rows selected

truncate table STATS$FILESTATXS;

truncate table STATS$LATCH;

truncate table STATS$LATCH_CHILDREN;

truncate table STATS$LATCH_MISSES_SUMMARY;

truncate table STATS$LATCH_PARENT;

truncate table STATS$LIBRARYCACHE;

truncate table STATS$BUFFER_POOL_STATISTICS;

truncate table STATS$ROLLSTAT;

truncate table STATS$ROWCACHE_SUMMARY;

truncate table STATS$SGA;

truncate table STATS$SGASTAT;

truncate table STATS$SYSSTAT;

truncate table STATS$SESSTAT;

truncate table STATS$SYSTEM_EVENT;

truncate table STATS$SESSION_EVENT;

truncate table STATS$BG_EVENT_SUMMARY;

truncate table STATS$WAITSTAT;

truncate table STATS$ENQUEUESTAT;

truncate table STATS$SQL_SUMMARY;

truncate table STATS$SQL_STATISTICS;

truncate table STATS$SQLTEXT;

truncate table STATS$PARAMETER;

delete from STATS$SNAPSHOT;

delete from STATS$DATABASE_INSTANCE;

commit;

D:\oracle\ora81\RDBMS\ADMIN>sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on 星期二 12月 3 16:54:53 2002

(c) Copyright 2000 Oracle Corporation.All rights reserved.

请输入口令:

连接到:

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

With the Partitioning option

JServer Release 8.1.7.0.0 - Production

SQL> select instance_name,host_name,version,startup_time from v$instance;

INSTANCE_NAMEHOST_NAME VERSIONSTARTUP_TIME

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

eygleAM-SERVER 8.1.7.0.022-11月-02

SQL>

D:\oracle\ora92\rdbms\admin>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 7月 10 19:18:54 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options

JServer Release 9.2.0.3.0 - Production

SQL>

SQL> select file_name from dba_data_files;

FILE_NAME

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

D:\ORACLE\ORADATA\EYGLE\SYSTEM01.DBF

D:\ORACLE\ORADATA\EYGLE\TEMP01.DBF

……

D:\ORACLE\ORADATA\EYGLE\HH_AM01.ORA

已选择24行。

SQL>

SQL> create tablespace perfstat

2datafile 'd:\oracle\oradata\eygle\perfstat.dbf'

3size 500M

4extent management local;

表空间已创建。

SQL>

E:\Oracle\ora92\rdbms\admin>dir /w sp*

驱动器 E 中的卷没有标签。

卷的序列号是 ACC3-4340

E:\Oracle\ora92\rdbms\admin 的目录

spauto.sqlspcpkg.sqlspcreate.sqlspctab.sqlspcusr.sqlspdoc.txt

spdrop.sqlspdtab.sqlspdusr.sqlsppurge.sqlsprepins.sqlspreport.sql

sprepsql.sqlsptrunc.sqlspuexp.parspup816.sqlspup817.sqlspup90.sql

18 个文件510,296 字节

0 个目录4,146,565,120 可用字节

SQL> @spcreate

.

.

Specify PERFSTAT user's defaulttablespace

输入 default_tablespace 的值:perfstat

Using perfstat for the default tablespace

用户已更改。

用户已更改。

Specify PERFSTAT user's temporary tablespace

输入 temporary_tablespace 的值:temp

... Creating PERFSTAT user ...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

Specify PERFSTAT password

输入 perfstat_password 的值: 123

123

PL/SQL 过程已成功完成。

create user perfstat identified by 123

*

ERROR 位于第 1 行:

ORA-00988: 缺少或无效口令

….

Creating Package STATSPACK...

程序包已创建。

没有错误。

Creating Package Body STATSPACK...

程序包主体已创建。

没有错误。

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL> host dir *.lis

驱动器 D 中的卷没有标签。

卷的序列号是 5070-5982

D:\oracle\ora81\RDBMS\ADMIN 的目录

2002-12-0317:25204 spcpkg.lis

2002-12-0317:252,276 spctab.lis

2002-12-0317:253,965 spcusr.lis

2002-12-0317:231,187 spdtab.lis

2002-12-0317:24351 spdusr.lis

5 个文件7,983 字节

0 个目录3,965,304,832 可用字节

SQL> host find “ORA-“ *.lis

SQL> host find "err" *.lis

---------- SPAUTO.LIS

---------- SPCPKG.LIS

SPCPKG complete. Please check spcpkg.lis for any errors.

---------- SPCTAB.LIS

SPCTAB complete. Please check spctab.lis for any errors.

---------- SPCUSR.LIS

SPCUSR complete. Please check spcusr.lis for any errors.

---------- SPDTAB.LIS

$ ls *.lis

spauto.lisspcpkg.lisspctab.lisspcusr.lisspdtab.lisspdusr.lis

$ grep ORA- *.lis

$ grep err *.lis

spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.

spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.

spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.

spdtab.lis:SPDTAB complete. Please check spdtab.lis for any errors.

spdusr.lis:SPDUSR complete. Please check spdusr.lis for any errors.

SQL> @spdrop.sql

.

.

.

同义词已丢弃。off;

视图已丢掉。

同义词已丢弃。

视图已丢掉。

同义词已丢弃。

用户已丢弃

NOTE:

SPDUSR complete. Please check spdusr.lis for any errors.

SQL>

SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

JOB LOG_USERLAST_DATENEXT_DATEINTERVAL

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

22 PERFSTAT 2002-12-5:14:33:26 2002-12-5 14:43:00 trunc(SYSDATE+1/144,'MI')

SQL> execute dbms_job.remove('22')

PL/SQL procedure successfully completed

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID

STATSPACK report for

DB NameDB IdInstanceInst Num ReleaseOPS Host

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

EYGLE1277924236 eygle1 8.1.7.0.0NOAM-SERVER

:ela:=;

*

ERROR 位于第 4 行:

ORA-06550: 第 4 行, 第 17 列:

PLS-00103: 出现符号 ";"在需要下列之一时：

(-+modnotnull<an identifier>

<a double-quoted delimited-identifier><a bind variable>avg

countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute

foralltimetimestampintervaldate

<a string literal with character set specification>

<a number><a single-quoted SQL string>

符号 "null" 被替换为 ";" 后继续。

ORA-06550: 第 6 行, 第 16 列:

PLS-00103: 出现符号 ";"在需要下列之一时：

(-+modnotnull<an identifier>

<a double-quoted delimited-identifier><a bind variable>avg

countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute

foralltimetimestampintervaldate

<a stri

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);

SQL> @spauto

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Note that this job number is needed when modifying or removing

the job:

JOBNO

----------

28

Job queue process

~~~~~~~~~~~~~~~~~

Below is the current setting of the job_queue_processes init.ora

parameter - the value for this parameter must be greater

than 0 to use automatic statistics gathering:

NAMETYPEVALUE

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

job_queue_processesinteger 5

Next scheduled run

~~~~~~~~~~~~~~~~~~

The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC

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

28 15-AUG-0316:00:00

SQL>execute statspack.snap

PL/SQL procedure successfully completed.

SQL>execute statspack.snap

PL/SQL procedure successfully completed.

SQL>@spreport.sql

…

SQL> exec statspack.snap;

BEGIN statspack.snap; END;

*

ERROR at line 1:

ORA-01401: inserted value too large for column

ORA-06512: at "PERFSTAT.STATSPACK", line 978

ORA-06512: at "PERFSTAT.STATSPACK", line 1612

ORA-06512: at "PERFSTAT.STATSPACK", line 71

ORA-06512: at line 1

select l_snap_id

, p_dbid

, p_instance_number

, substr(sql_text,1,31)

．．．．．．．．．．．

SQL> alter system set job_queue_processes = 6;

System altered

SQL> alter system set job_queue_processes = 6 scope=both;

系统已更改。

SQL> alter system set timed_statistics = true;

System altered

SQL>

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;
/

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;