哪位兄弟请帮我解答一下,我的问题如下:
我下代码中我想把 vmi 这表中的数据按一定条件抓取出来后,放到邮件的正文中,请问怎么做?如果我按下面的程式执行则会报错
CREATE OR REPLACE PROCEDURE SENDMAILTest AS
smtpServer varchar2(100) := 'mail.feiliks.com';
smtpPort varchar2(8) :='25';
senderAddress varchar2(100):='[email protected]';
senderPassowrd varchar2(100):='feili,000';
receiverAddress varchar2(100):='[email protected]';
mail_subjct varchar2(100) := 'Test From Juntao ';
mail_body varchar2(4000);
conn utl_smtp.CONNECTION;
CRLF VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
v_arearecord vmi_allocate_stock%rowtype;BEGIN
select a.* into v_arearecord from vmi a where a.rec_asn_no = '0000000586' ;
conn:= utl_smtp.open_connection(smtpServer,smtpPort);
utl_smtp.ehlo(conn,smtpServer);
utl_smtp.command(conn,'AUTH LOGIN');
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(senderAddress))));
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(senderPassowrd))));
utl_smtp.mail( conn,senderAddress);
utl_smtp.rcpt( conn,receiverAddress);
utl_smtp.open_data(conn); --打开流
utl_smtp.write_data(conn, mail_subjct);
mail_body := v_arearecord ;
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(mail_body));
utl_smtp.write_data(conn, CRLF);
utl_smtp.close_data(conn);--关闭流
utl_smtp.quit( conn ); --关闭连接
END;
我下代码中我想把 vmi 这表中的数据按一定条件抓取出来后,放到邮件的正文中,请问怎么做?如果我按下面的程式执行则会报错
CREATE OR REPLACE PROCEDURE SENDMAILTest AS
smtpServer varchar2(100) := 'mail.feiliks.com';
smtpPort varchar2(8) :='25';
senderAddress varchar2(100):='[email protected]';
senderPassowrd varchar2(100):='feili,000';
receiverAddress varchar2(100):='[email protected]';
mail_subjct varchar2(100) := 'Test From Juntao ';
mail_body varchar2(4000);
conn utl_smtp.CONNECTION;
CRLF VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
v_arearecord vmi_allocate_stock%rowtype;BEGIN
select a.* into v_arearecord from vmi a where a.rec_asn_no = '0000000586' ;
conn:= utl_smtp.open_connection(smtpServer,smtpPort);
utl_smtp.ehlo(conn,smtpServer);
utl_smtp.command(conn,'AUTH LOGIN');
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(senderAddress))));
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(senderPassowrd))));
utl_smtp.mail( conn,senderAddress);
utl_smtp.rcpt( conn,receiverAddress);
utl_smtp.open_data(conn); --打开流
utl_smtp.write_data(conn, mail_subjct);
mail_body := v_arearecord ;
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(mail_body));
utl_smtp.write_data(conn, CRLF);
utl_smtp.close_data(conn);--关闭流
utl_smtp.quit( conn ); --关闭连接
END;
应该
mail_body := v_areadrecord.项目
declare mailhost varchar2(30) := '邮件服务器ip'; sender varchar2(30) := '发邮件人地址'; recipient varchar2(30) := 收邮件地址; conn utl_smtp.connection; mesghead varchar2(500); mesgbody varchar2(2000);BEGIN
conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.command(conn, 'AUTH LOGIN'); utl_smtp.command(conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('发邮件人地址')))); utl_smtp.command(conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('密码'))));
utl_smtp.helo(conn, mailhost);
utl_smtp.mail(conn, sender);
utl_smtp.rcpt(conn, recipient);
utl_smtp.open_data(conn); mesghead :=
'Date:' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') ||
utl_tcp.CRLF ||
'From:' || sender || utl_tcp.CRLF ||
'Subject: Sending Mail From Budget Forewarn System' ||
utl_tcp.CRLF ||
'To: ' || recipient || utl_tcp.CRLF || utl_tcp.CRLF || '' ||
utl_tcp.CRLF; utl_smtp.write_data(conn, mesghead); mesgbody :=
'电脑部预算预警指标(一月份):' || utl_tcp.CRLF ||
' 1、预算执行率:,' || utl_tcp.CRLF ||
' 2、分科目预算执行情况:' || utl_tcp.CRLF ||
'' || utl_tcp.CRLF || '此邮件为系统自动发送,勿需回复!' || utl_tcp.CRLF; utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(mesgbody)); utl_smtp.close_data(conn); utl_smtp.quit(conn); --EXCEPTION --WHEN OTHERS THEN -- Handle the errorEND;
Compilation errors for PROCEDURE BMS.SENDMAILTESTError: PLS-00382: 表达式类型错误
Line: 31
Text: mail_body := v_arearecord ;Error: PL/SQL: Statement ignored
Line: 31
Text: mail_body := v_arearecord ;
DECLARE
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION(''mail.feiliks.com'');
UTL_SMTP.helo (c, ''mail.feiliks.com'');
UTL_SMTP.mail (c, '[email protected]');
UTL_SMTP.rcpt (c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Kronos Reminder Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Test email using Oracle UTL_SMTP package');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world1!');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world2!');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'End.');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
也就是 mail_body 的内容是用 Select 语句捞出来的。
(SELECT user_name, user_id FROM all_users)
LOOP
UTL_SMTP.write_data (c,
UTL_TCP.crlf
|| v_row.username
|| TO_CHAR(v_row.user_id) )
);
END LOOP;如果有中文,则
FOR v_row IN
(SELECT user_name, user_id FROM all_users)
LOOP
UTL_SMTP.write_raw_data
(c,
UTL_RAW.cast_to_raw
(CONVERT ( UTL_TCP.crlf
|| v_row.user_name
|| TO_CHAR(v_row.user_id),
'ZHS16GBK'
)
)
);
END LOOP;