?
Solved

Emailing report to 2500 users

Posted on 2011-04-18
4
Medium Priority
?
247 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,

0
Comment
Question by:gs79
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 35422022
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.
0
 

Author Comment

by:gs79
ID: 35445504
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
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 35445661
Don't have time right now but the following should point you the right direction:

http://www.orafaq.com/wiki/Send_mail_from_PL/SQL
http://www.orafaq.com/wiki/DBMS_SCHEDULER

Create the stored procedure using what you learn from the first, then schedule a job for it using what you learn from the second.  Post here if you encounter difficulty and I'll try to take a look where it went wrong.
0
 

Author Comment

by:gs79
ID: 35933082
The project is in pending..i will be sure trying on those lines..thanks for your suggestions..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

840 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