asked on

Emailing report to 2500 users

We have 2000 users and a report (basically sql query) that has user_id and corresponding email address.. I have a requirement to run this query on a nightly basis and send this out to the users. The users should get the data specific to their user id. The report looks like below:

select user_id, col1, col2, col3, email
from xyz

Now the users should recieve the data corresponding to his user_id:

select user_id, col1, col2, col3, email
from xyz
where user_id = :user_id

Also I need to schedule this report probably using dbms scheduler and send the email out for the user's corresponding user id. Another requirement is: Since the query takes a long time to run for even for an user_id, I need to divide this job into 4 concurrent processes and each process sends the report for 500 users parallely..hel

Please help me write this query..

thanks,

johanntagle

Does xyz only have one row for each user_id? Think you can do it via a stored procedure

create procedure mail_out (start_id integer, end_id integer)
cursor reports is
select user_id, col1, col2, col3, email
from xyz
where user_id between start_id and end_id;
begin
for cur in reports loop
/*do mailing out procedure here, referring to each row elements as cur.user_id, etc */
end loop;
end;

Then you can have 4 jobs calling the procedure, having different values start_id and end_id as your range for user_id for each call.

If there are multiple rows for each user_id, then you need to add an order by user_id in the cursor definition, and only send out email if it detects that the value of cur.user_id is new.

gs79

ASKER

can you please help me with the procedure to send email. I have not used dbms_email utility before..also the program needs to be scheduled on a daily basis..
Please help

Thanks..
Girish

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

The project is in pending..i will be sure trying on those lines..thanks for your suggestions..