[求助]，Oracle使用utl_smtp发送Email, Email中含有特殊字符的问题
紧急求助啊，我在Oracle中，使用utl_smtp来发送Email,如果Email里面包含 £ 英镑符号，发出去的邮件 £ 符号就会变成#但是我在 Sqlplus里面可以正确显示，在PHP里面也能正确读取
字段用的是VARCHAR2
以下是发送Email的代码。
PROCEDURE SendEMail(p_fromaddress IN VARCHAR2
,p_toaddress IN VARCHAR2
,p_subject IN VARCHAR2
,p_body IN VARCHAR2
,p_domain IN VARCHAR2 DEFAULT 'RDL3'
,p_fromnatural IN VARCHAR2 DEFAULT NULL
,p_tonatural IN VARCHAR2 DEFAULT NULL
,p_content_type IN VARCHAR2 DEFAULT NULL
-- for html set p_content_type=>'text/html'
,p_IgnoreNoBulkEmail IN VARCHAR2 DEFAULT 'N'
,p_sentdatetime IN DATE DEFAULT SYSDATE
)
IS
c utl_smtp.connection;
i NUMBER(4);
v_body VARCHAR2(32767);
v_choplength NUMBER(8,0);
v_emailcase VARCHAR2(150);
v_fromnatural VARCHAR2(80);
v_nobulkemail BOOLEAN := FALSE;
v_serverIP VARCHAR2(150);
v_subamount NUMBER(8,0);
v_tonatural VARCHAR2(80);
v_toaddress VARCHAR2(80);
CURSOR c_can(p_email IN VARCHAR2) IS
SELECT NVL(no_bulk_emails,'N') NoBulkEmails
FROM CANDIDATES can
WHERE p_email IN (can.email ,can.email2 )
AND NVL(no_bulk_emails,'N') <> 'N';
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
/* are mail addresses UPPER or lower case? */
SELECT descr
INTO v_emailcase
FROM REF_CODES
WHERE red#code = 'ECASE'
AND code = 'CAND';
IF v_emailcase = 'UPPER' THEN
v_toaddress := UPPER(p_toaddress);
ELSE
v_toaddress := LOWER(p_toaddress);
END IF;
dbms_output.put_line(v_emailcase);
v_tonatural := NVL(p_tonatural,p_toaddress);
v_fromnatural := NVL(p_fromnatural,p_fromaddress);
dbms_output.put_line(v_toaddress);
IF p_IgnoreNoBulkEmail = 'N' THEN
/* Check if this recipient has opted out of bulk mails */
/* If the email address is not unique, and any of the records */
/* have the bulk indicator set to Y, assume that this ADDRESS */
/* doesn't want bulk mails. */
FOR r_can IN c_can(v_toaddress) LOOP
dbms_output.put_line('r_cannobulkemails '||r_can.NoBulkEmails);
IF r_can.NoBulkEmails = 'Y' THEN
v_nobulkemail := TRUE;
dbms_output.put_line('nobulkemails = true');
END IF;
END LOOP;
END IF;
v_nobulkemail := FALSE;
IF p_IgnoreNoBulkEmail = 'Y' OR v_nobulkemail = False THEN
dbms_output.put_line('p_ignore '||p_IgnoreNoBulkEmail);
/* Send Mail */
/* Get Server IP address */
SELECT descr
INTO v_serverIP
FROM REF_CODES
WHERE red#code = 'SMTP'
AND code = 'SERVER';
/* open smtp connection */
/* added pauses to avoid 554 SMTP synchronization error which is caused by sending part of the */
/* SMTP conversation without waiting for a response, this is a spammer thing to do AND lots of */
/* servers reject mails on this basis (including us!)*/
dbms_output.put_line('Start SMTP conversation');
dbms_output.put_line('From '||v_fromnatural||' <'||p_fromaddress||'>');
c := utl_smtp.open_connection(v_serverIP);
dbms_lock.sleep(0.5);
--utl_smtp.helo(c, p_domain);
utl_smtp.ehlo(c, p_domain);
dbms_lock.sleep(0.5);
utl_smtp.mail(c, p_fromaddress);
--dbms_lock.sleep(0.5);
utl_smtp.rcpt(c, p_toaddress);
--dbms_lock.sleep(0.5);
utl_smtp.open_data(c);
send_header('From', '"'||v_fromnatural||'" <'||p_fromaddress||'>');
--dbms_lock.sleep(0.5);
send_header('Date', TO_CHAR(p_sentdatetime,'Dy, DD Mon YYYY HH24:mi:ss'));
--dbms_lock.sleep(0.5);
send_header('Reply-to', p_fromaddress);
--dbms_lock.sleep(0.5);
send_header('To', '"'||v_tonatural||'" <'||p_toaddress||'>');
--dbms_lock.sleep(0.5);
send_header('Subject', p_subject);
--dbms_lock.sleep(0.5);
IF p_content_type IS NOT NULL THEN
send_header('Content-Type',p_content_type);
--dbms_lock.sleep(0.5);
END IF;
v_body := p_body;
utl_smtp.write_data(c,utl_tcp.crlf);
--dbms_lock.sleep(0.5);
utl_smtp.write_data(c,utl_tcp.crlf);
--dbms_lock.sleep(0.5);
WHILE LENGTH(v_body) > 0 LOOP
BEGIN
IF INSTR(v_body,utl_tcp.crlf) BETWEEN 1 AND 2000 THEN
v_choplength := LENGTH(SUBSTR(v_body,1,INSTR(v_body,utl_tcp.crlf)));
v_subamount := 1;
ELSIF LENGTH(v_body) > 2000 THEN
v_choplength := 2000;
v_subamount := 0;
ELSE
v_choplength := LENGTH(v_body);
v_subamount := 0;
END IF;
IF SUBSTR(v_body,1,v_choplength - v_subamount) IS NOT NULL THEN
utl_smtp.write_data(c,SUBSTR(v_body,1,v_choplength - v_subamount));
END IF;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Exception: '||SQLCODE);
RAISE;
END;
v_body := SUBSTR(v_body,v_choplength + 1);
END LOOP;
utl_smtp.close_data(c);
dbms_lock.sleep(0.5);
utl_smtp.quit(c);
END IF;
END SendEMail;
[ 本帖最后由 shiqing 于 2010-3-1 12:36 编辑 ]
