lamija
asked on
Oracle Report 6i - send mail
Dear experts
I have 9i database and Report 6i.
I want to send salary paper on employee's mail.
One paper to one employee, mean payslip only mailed to the related employee.
I have Report of all payslip and I can save it in rtf format.
What is the suitable solutions?
Best regards
I have 9i database and Report 6i.
I want to send salary paper on employee's mail.
One paper to one employee, mean payslip only mailed to the related employee.
I have Report of all payslip and I can save it in rtf format.
What is the suitable solutions?
Best regards
Reports server 9i/10g is able to send mails.
You have to think about migration to
newer version of reports.
But if you won't to migrate
you have to write stored procedure and to invoke it
from Reports:
CREATE OR REPLACE PROCEDURE SP_SENDMAIL
(
mail_server in varchar2,
sender in varchar2,
recipient in varchar2,
subject in varchar2,
message in varchar2)
is
c utl_smtp.connection;
mesg varchar2 (4000);
crlf varchar2(2) := chr(13) || chr(10);
mlength number(4);
mdone number(4) := 0;
mpos number(3) :=1;
linewidth number(2) := 60;
begin
mlength:=length(message);
c := utl_smtp.open_connection(m ail_server , 25);
utl_smtp.helo(c, mail_server);
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c,recipient) ;
mesg := 'Date: ' || to_char(sysdate, 'dd Mon yy hh24:mi:ss');
mesg := mesg || crlf;
mesg := mesg || 'From: ' || sender || '<' ||
sender || '>';
mesg := mesg || crlf;
mesg := mesg || 'To: ' || recipient || '<' ||
recipient || '>';
mesg := mesg || crlf;
mesg := mesg || 'Subject: ' || subject;
mesg := mesg || crlf;
mesg := mesg || '' || crlf;
while mdone < mlength and mpos < mlength
loop
if (mlength - mdone) < linewidth then
mesg := mesg || substr(message, mpos, mlength - mdone) || crlf;
mdone := mdone + (mlength - mdone);
mpos := mlength;
else
mesg := mesg || substr(message, mpos, 60) || crlf;
mpos := mpos + 60;
end if;
end loop;
utl_smtp.data(c, mesg);
utl_smtp.quit(c);
exception
when utl_smtp.transient_error or
utl_smtp.permanent_error then
utl_smtp.quit(c);
raise_application_error(-2 0000, 'Failed to send mail due to following
' || sqlerrm);
end sp_sendmail;
You have to think about migration to
newer version of reports.
But if you won't to migrate
you have to write stored procedure and to invoke it
from Reports:
CREATE OR REPLACE PROCEDURE SP_SENDMAIL
(
mail_server in varchar2,
sender in varchar2,
recipient in varchar2,
subject in varchar2,
message in varchar2)
is
c utl_smtp.connection;
mesg varchar2 (4000);
crlf varchar2(2) := chr(13) || chr(10);
mlength number(4);
mdone number(4) := 0;
mpos number(3) :=1;
linewidth number(2) := 60;
begin
mlength:=length(message);
c := utl_smtp.open_connection(m
utl_smtp.helo(c, mail_server);
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c,recipient)
mesg := 'Date: ' || to_char(sysdate, 'dd Mon yy hh24:mi:ss');
mesg := mesg || crlf;
mesg := mesg || 'From: ' || sender || '<' ||
sender || '>';
mesg := mesg || crlf;
mesg := mesg || 'To: ' || recipient || '<' ||
recipient || '>';
mesg := mesg || crlf;
mesg := mesg || 'Subject: ' || subject;
mesg := mesg || crlf;
mesg := mesg || '' || crlf;
while mdone < mlength and mpos < mlength
loop
if (mlength - mdone) < linewidth then
mesg := mesg || substr(message, mpos, mlength - mdone) || crlf;
mdone := mdone + (mlength - mdone);
mpos := mlength;
else
mesg := mesg || substr(message, mpos, 60) || crlf;
mpos := mpos + 60;
end if;
end loop;
utl_smtp.data(c, mesg);
utl_smtp.quit(c);
exception
when utl_smtp.transient_error or
utl_smtp.permanent_error then
utl_smtp.quit(c);
raise_application_error(-2
' || sqlerrm);
end sp_sendmail;
ASKER
ok
If I choose migration to Report 9i how can I send mail?
If I choose migration to Report 9i how can I send mail?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you want to send from oracle database, then you should have a table
where to email address is stored and text you want to send can also be in
CLOB field and also the subject we can have. something like the below :
mail_payslip table
-------------------
to_address varchar2(100),
subject varchar2(1000),
mail_text clob
process_flag varchar2(1) -- this we can have it Y or N
We can write a pl/sql block, to pick all records ( with process_flag as 'Y') from the above table and send
the text which we have stored in mail_text to the email address which we have given in to_address field along
with the subject.
Thanks