Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Question Unanswered: Trying to schedule and send email

    Hello experts,

    I am using oracle 10 client and connecting to an oracle 10 database. I am still relatively new to oracle and I am looking into a way to schedule and send an email to one or multiple recipients(very simple email that shows a link to a web page). Is there any way to do this in oracle. All I need is really sender, recipient, subject and body and in that body I need to attach a hyperlink. Is this possible. I also need to schedule this email to run every Monday. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Check out the UTL_SMTP package. See documentation at

    http://download-east.oracle.com/docs...tm#sthref15586


    Use DBMS_SCHEDULAR to schedule the run of your code. See documentaion at

    http://download-east.oracle.com/docs...htm#sthref6596
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Search this link for 'Sending E-Mail from PL/SQL'. To schedule, you can use dbms_scheduler, dbms_job, etc.

  4. #4
    Join Date
    Apr 2006
    Posts
    140
    Thanks everyone for responding.
    O.k I have figured out how to send an email and have created a stored procedure that fetches data from a table and sends out an email to recipients in that table. Looks like it is working.

    Now the DBMS_scheduler is the part I do not understand. Am I creating a stored procedure or something to schedule this once a week. All I need is to make a call to the stored procedure I just created for sending mail that will fire every Monday morning. Sorry, I just didn't quite understand what I was reading. Plus I did not understand how to put it together. Could someone provide some sample code for scheduling on a weekly basis.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I just didn't quite understand what I was reading.
    Read it once again, until you understand it

    This sample script shows how to schedule a job to run weekly, on Friday at 10 o'clock:
    Code:
    BEGIN
       dbms_scheduler.create_job
                        (job_name             => 'SCOTT.RUN_SCOTTS_PROC',
                         job_type             => 'STORED_PROCEDURE',
                         job_action           => 'SCOTT.SCOTTS_PROC',
                         start_date           => TRUNC (SYSDATE + 1) + 1 / 24,
                         repeat_interval      => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=10;',
                         enabled              => TRUE,
                         auto_drop            => FALSE,
                         comments             => 'Converted from job 152152'
                        );
    END;

  6. #6
    Join Date
    Apr 2006
    Posts
    140
    Hey Littlefoot,

    hahaha it's that easy eh. I think I need to go home
    What I was getting at though is how do I wrap this begin and end together or do I simply copy and paste make the necessary changes to variables and execute.

    Do I not have to say create procedure or whatever. Not sure what it is that I'm creating.

    Code:
    CREATE OR REPLACE PROCEDURE Job1
    BEGIN
       dbms_scheduler.create_job
                        (job_name             => 'SCOTT.RUN_SCOTTS_PROC',
                         job_type             => 'STORED_PROCEDURE',
                         job_action           => 'SCOTT.SCOTTS_PROC',
                         start_date           => TRUNC (SYSDATE + 1) + 1 / 24,
                         repeat_interval      => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=10;',
                         enabled              => TRUE,
                         auto_drop            => FALSE,
                         comments             => 'Converted from job 152152'
                        );
    END;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DBMS_SCHEDULER.CREATE_JOB creates a job; that's it. It needs BEGIN-END as it is a PL/SQL product - a package procedure so - either put it into BEGIN-END, or run with EXECUTE package_name.procedure_name.

    Go home.

  8. #8
    Join Date
    Apr 2006
    Posts
    140
    hahaha. Alright then. Got it. Thanks Littlefoot. I'm outta here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •