Automate Weekly Report


i have requirement to automate weekly report with the records of a particular table data with certain conditions. Then i have to write dbms_scheduler to call this procedure to generate the report and send to the appropriate persons.

I wrote a query as follows:

select sid, TO_char(date_in_class,‘MM-DD-YYYY’)

from test

where sid in(‘ST1234’,‘ST5678’)

and date_in between to_date(‘04/03/2011’,‘mm/dd/yyyy’) and to_date(‘04/09/2011’,‘mm/dd/yyyy’);

i would like to put this in a procedure and use the date_in as the start and end dates for the week, which should automatically get the weekly dates based on the week i.e. Typical Sunday to Saturday week calculation.

Can someone please shed some light how to accomplish this?