7 Replies Latest reply: Mar 18, 2013 8:26 AM by sushaant RSS

    Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB

    maggie
      Good day All,

      I need your guidance and help in scheduling the stored procedure using dbms_schedular to run on

      - End of every month (can be 28th, 30, or 31st) of every month
      - Run on 1st of every month
      - Last seventh day before of every month

      Based on the examples on the web, I came up with following
      BEGIN
      DBMS_SCHEDULER.create_job (
      job_name => 'schemaname.jobname',
      job_type => ’STORED_PROCEDURE’,
      job_action => 'BEGIN schemaname.Procedure name; END;',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
      end_date => NULL,
      enabled => TRUE,
      comments => 'Job defined entirely by the CREATE JOB procedure “schemaname.yourjobname”.');
      END;
      /

      Please guide me how to change my dbms_schedular.Create_job based on the requirements and also how do I run the job after setting the repeat_interval based on the requirements. How do I validate the job run has taken place.
      Previously, I set using cron jobs (linux, unix) and windows schedular (win env) but I am totally new to dbms_schedular concept.
      Your experience and guidance will be of immense help to me. Kindly create a script based on example I provided and let me know if I am in right direction.

      Regards
      Maggie
        • 1. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
          Sunny kichloo
          Refer this link DBMS_SCHEDULER is Nicely Explained

          http://guides.wikinut.com/A-Brief-overview-of-Oracle-s-inbuilt-DBMS-SCHEDULER-Package/3n6umeq_/
          • 2. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
            maggie
            Thanks for the link. I have seen many examples my self but , I was hoping if my code to be altered and show how to run the job. I will also be trying out myself. Just wanted to know if any loops need to be incorporated in the code. More leads are welcome. Thanks for the help
            • 3. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
              maggie
              Is it possible to have error handling in dbms_schedular.create job , in case the job does not go through, how do we catch it and identify the job did not perform. Please show me the relevant examples. As allways thank you for great help

              Maggie
              • 4. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
                spajdy
                Could you be more specific ?
                In general in PL/SQL you can catch exception in exception handler. Each PL/SQL block have this sections:
                1/ declration - optional
                2/ code - required
                3/ exception - optional
                DECLARE
                -- declare types,variables and local function/procedures there
                ...
                BEGIN
                -- do some stuff
                ... 
                EXCEPTION
                WHEN no_data_found THEN
                  -- handle no data found exception there
                  ...
                WHEN too_many_rows THEN
                  -- handle no too many rows there
                ...
                END;
                /
                • 5. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
                  maggie
                  Based on the example below, how do we catch error handling process. How do we know if the scheduled job was not performed due to any error caused?

                  BEGIN
                  DBMS_SCHEDULER.create_job (
                  job_name => 'schemaname.jobname',
                  job_type => ’STORED_PROCEDURE’,
                  job_action => 'BEGIN schemaname.Procedure name; END;',
                  start_date => SYSTIMESTAMP,
                  repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
                  end_date => NULL,
                  enabled => TRUE,
                  comments => 'Job defined entirely by the CREATE JOB procedure “schemaname.yourjobname”.');
                  END;
                  • 6. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
                    spajdy
                    >
                    Based on the example below, how do we catch error handling process. How do we know if the scheduled job was not performed due to any error caused?
                    >

                    1/ schemaname.Procedure name - if you have exception handler in this procedure and hadle all posible exception then call to this procedure never failed. So if you are looking for problem in your procedure you must implement your own error logging logic.
                    2/ Scheduler have those views
                    dba_scheduler_job_run_details - it containt detail about each job run there you could find when run was started, what the required start date was, duration, status and so on
                    dba_scheduler_job_log - there are logged important job events - start of job, end of job, for chain jobs start and end of steps and so on

                    in both views there is column additional_info where are exception messages stored in case of error.

                    Is this you are looking for ?
                    • 7. Re: Need help scheduling jobs using DBMS_SCHEDULAR.CREATE_JOB
                      sushaant
                      You need to perform error handling in the package/procedure name you are calling in job action tag. Apart from this if the job failed due to some reason then you can get error message in the DB alert log. As well you can check dba_Scheduler_job_run_details & dba_scheduler_job_log tables with the job_name. If you are not able to find exact job name then you can query into user_scheduler_jobs table.

                      Cheers
                      Sush