create or replace
TRIGGER "PLMDEBUG".TRI_Z63_SENDMAIL
AFTER INSERT ON PLMDEBUG.Z63
FOR EACH ROW
DECLARE pragma autonomous_transaction;
mailhost VARCHAR2(30) := 'sp-mail02.lawsgroup.com'; -- local database host
msg_from varchar2(50) :='pengfei.qiu@lawsgroup.com';
msg_to varchar2(50);
msg_subject varchar2(50);
msg_body varchar2(4000);
message varchar2(4000);
conn utl_smtp.connection;
BEGIN
IF INSERTING THEN
SELECT DISTINCT z20parval INTO msg_to
FROM z20,z63
WHERE z20comcod='LT'
AND z20partyp='MAIL'
AND z20parcod =:NEW.z63syscod
AND :NEW.z63emlflg='Y';
msg_subject:=:NEW.z63errtyp;
msg_body:=:NEW.z63errmsg1;
msg_to:='pengfei.qiu@lawsgroup.com';
message :='From: sz.itsupport ['||msg_from ||']'||utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Date: ' || to_char(sysdate, 'dd mon yy hh24:mi:ss') || utl_tcp.crlf ||
'Content-Type:text/html;Charset=GB2312' || utl_tcp.crlf ||
'Subject: ' || msg_subject ||utl_tcp.crlf ||
'<html> Hello:<br/>'||msg_body||'</html>';
conn := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(conn, mailhost);
utl_smtp.mail(conn, msg_from);
utl_smtp.rcpt(conn, msg_to);
utl_smtp.open_data(conn);
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(convert(message,'ZHS16GBK')));
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
END IF;
END;
TRIGGER "PLMDEBUG".TRI_Z63_SENDMAIL
AFTER INSERT ON PLMDEBUG.Z63
FOR EACH ROW
DECLARE pragma autonomous_transaction;
mailhost VARCHAR2(30) := 'sp-mail02.lawsgroup.com'; -- local database host
msg_from varchar2(50) :='pengfei.qiu@lawsgroup.com';
msg_to varchar2(50);
msg_subject varchar2(50);
msg_body varchar2(4000);
message varchar2(4000);
conn utl_smtp.connection;
BEGIN
IF INSERTING THEN
SELECT DISTINCT z20parval INTO msg_to
FROM z20,z63
WHERE z20comcod='LT'
AND z20partyp='MAIL'
AND z20parcod =:NEW.z63syscod
AND :NEW.z63emlflg='Y';
msg_subject:=:NEW.z63errtyp;
msg_body:=:NEW.z63errmsg1;
msg_to:='pengfei.qiu@lawsgroup.com';
message :='From: sz.itsupport ['||msg_from ||']'||utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Date: ' || to_char(sysdate, 'dd mon yy hh24:mi:ss') || utl_tcp.crlf ||
'Content-Type:text/html;Charset=GB2312' || utl_tcp.crlf ||
'Subject: ' || msg_subject ||utl_tcp.crlf ||
'<html> Hello:<br/>'||msg_body||'</html>';
conn := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(conn, mailhost);
utl_smtp.mail(conn, msg_from);
utl_smtp.rcpt(conn, msg_to);
utl_smtp.open_data(conn);
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(convert(message,'ZHS16GBK')));
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
END IF;
END;