Emailing report to 2500 users

Posted on 2011-04-18
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..


Question by:gs79
    LVL 24

    Expert Comment

    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;
      for cur in reports loop
         /*do mailing out procedure here, referring to each row elements as cur.user_id, etc */
      end loop;

    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 Comment

    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

    LVL 24

    Accepted Solution

    Don't have time right now but the following should point you the right direction:

    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.

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    759 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