oracle发送邮件

CREATE OR REPLACE PROCEDURE send_mail (smtp_server IN VARCHAR2,
port IN NUMBER,
login_user IN VARCHAR2,
login_pass IN VARCHAR2,
mailfrom IN VARCHAR2,
mailto IN VARCHAR2,
msg_head IN VARCHAR2,
msg_body IN CLOB)
AS
v_offset NUMBER;
v_ammount NUMBER;
v_position NUMBER;
v_length NUMBER;
v_mailto_tmp1 varchar2(1000);
v_mailto_tmp2 varchar2(1000);
mail_conn UTL_SMTP.CONNECTION;
BEGIN
mail_conn := UTL_SMTP.open_connection (smtp_server, port);
UTL_SMTP.helo (mail_conn, smtp_server);
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 (login_user))
)
);
UTL_SMTP.command (
mail_conn,
UTL_RAW.CAST_TO_VARCHAR2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (login_pass))
)
);
UTL_SMTP.mail (mail_conn, mailfrom);

v_offset := 1;
v_position := 1;
v_mailto_tmp2 := mailto;
v_length := length(v_mailto_tmp2);
LOOP
v_position:=instr(v_mailto_tmp2,';');
if v_position = 0 then
UTL_SMTP.rcpt (mail_conn, v_mailto_tmp2);
end if;
exit when v_position = 0;
v_length := length(v_mailto_tmp2);
v_mailto_tmp1 := trim(substr(v_mailto_tmp2,v_offset,v_position-1));
v_mailto_tmp2 := trim(substr(v_mailto_tmp2,v_position+1,v_length+1-v_position));
UTL_SMTP.rcpt (mail_conn, v_mailto_tmp1);
END LOOP;

UTL_SMTP.open_data (mail_conn);
UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw (msg_head));
v_offset := 1;
v_ammount := 1900;

WHILE v_offset < DBMS_LOB.getlength (msg_body)
LOOP
UTL_SMTP.write_raw_data (
mail_conn,
UTL_RAW.cast_to_raw (
DBMS_LOB.SUBSTR (msg_body, v_ammount, v_offset)
)
);
v_offset := v_offset + v_ammount;
v_ammount :=
LEAST (v_ammount, DBMS_LOB.getlength (msg_body) - v_ammount);
END LOOP;

UTL_SMTP.close_data (mail_conn);
UTL_SMTP.quit (mail_conn);
END;