We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Emailing report to 2500 users

gs79
gs79 asked
on
Medium Priority
257 Views
Last Modified: 2012-05-11
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,

Comment
Watch Question

Top Expert 2012

Commented:
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.

Author

Commented:
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
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
The project is in pending..i will be sure trying on those lines..thanks for your suggestions..
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.