Solved

Oracle Report 6i - send mail

Posted on 2007-11-20
4
2,112 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:lamija
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20325719
do you want to send mail from oracle database or from reports 6i ?

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
0
 
LVL 47

Expert Comment

by:schwertner
ID: 20326189
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(mail_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(-20000, 'Failed to send mail due to following
' || sqlerrm);
end sp_sendmail;



0
 
LVL 1

Author Comment

by:lamija
ID: 20330496
ok
If I choose migration to Report 9i how can I send mail?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 20334142
First at all you have to find a working or install an SMTP mail.server.
After that you have to install Oracle Forms/Reports server standalone
or find working Oracle Application Server 9i/10g with Oracle Forms/Reports server
installation on it.

Now you can either Reports Developer distribution list and to add mailing of reports
after creating or use a batch job that using binaries in the Reports server installation
maiils the report.

It is explained on the documentation of Oracle Forms/Reports server on the OTN.
Build Reports on the WEB sofar as I recall.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 71
Error executing command from server 6 49
Determine Who is Runnig my Bash Shell Script 4 79
Oracle - Create Procedure with Paramater 16 63
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now