Home » RDBMS Server » Server Administration » Making a dbms_scheduler job to execute in next 10 minutes (Oracle 11.2.0.2 on Solaris 8)
Making a dbms_scheduler job to execute in next 10 minutes [message #599255] Wed, 23 October 2013 05:36 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi,
I am trying to set up a dbms_scheduler job and to set up such that it should execute in next 10 minutes but what i am finding is that it is set up to execute only at the next day! Can someone help me on how I need to do the setup.

Here is the procedure and code of how I am setting up:

CREATE OR REPLACE PROCEDURE p
AS
   v_sql   VARCHAR2 (1000);
BEGIN
   v_sql := 'select junk from abc';

   EXECUTE IMMEDIATE v_sql;
EXCEPTION
   WHEN OTHERS
   THEN
	NULL;
END;
/

DECLARE
   v_program_name    VARCHAR2 (30);
   v_schedule_name   VARCHAR2 (30);
   v_job_name        VARCHAR2 (30);
   v_count            NUMBER;
   v_sql_string      VARCHAR2 (1000);
BEGIN
--create schedule
   v_schedule_name := 'P_SCHED';
   v_sql_string :=
      'SELECT COUNT(1) from user_SCHEDULER_SCHEDULES where SCHEDULE_name=:1';
   EXECUTE IMMEDIATE v_sql_string INTO v_count USING v_schedule_name;
   IF v_count = 0
   THEN
      v_sql_string :=
            ' BEGIN  '
         || 'DBMS_SCHEDULER.create_schedule ('
         || ' schedule_name          => '
         || CHR (39)
         || 'P_SCHED'
         || CHR (39)
         || ','
         || '   start_date            => trunc(sysdate)'
         || ','
         || '  repeat_interval         => '
         || CHR (39)
         || 'freq=MINUTELY;byhour=0;byminute=0;bysecond=10'
         || CHR (39)
         || ','
         || 'comments        =>'
         || CHR (39)
         || 'Schedule for the P_PROC stored proc to execute daily at 9.15 am'
         || CHR (39)
         || ');'
         || 'END;';
      DBMS_OUTPUT.PUT_LINE (v_sql_string);
      EXECUTE IMMEDIATE v_sql_string;
   END IF;

--create program
   v_program_name := 'P_PROG';
   v_sql_string :=
      'SELECT COUNT(1) from user_scheduler_programs where program_name=:1';

   EXECUTE IMMEDIATE v_sql_string INTO v_count USING v_program_name;

   IF v_count = 0
   THEN
      v_sql_string :=
            ' BEGIN  '
         || 'DBMS_SCHEDULER.create_program   ('
         || ' program_name         => '
         || CHR (39)
         || 'P_PROG'
         || CHR (39)
         || ','
         || ' program_type         => '
         || CHR (39)
         || 'STORED_PROCEDURE'
         || CHR (39)
         || ','
         || ' program_action         => '
         || CHR (39)
         || 'P'
         || CHR (39)
         || ','
         || 'enabled         => FALSE,'
         || 'comments        =>'
         || CHR (39)
         || 'Program definition for calling the P_PROC stored proc '
         || CHR (39)
         || ');'
         || 'END;';
      DBMS_OUTPUT.PUT_LINE (v_sql_string);

      EXECUTE IMMEDIATE v_sql_string;



--Enable the program 
      v_sql_string :=
            ' BEGIN  '
         || 'DBMS_SCHEDULER.enable    ('
         || ' name         => '
         || CHR (39)
         || 'P_PROG'
         || CHR (39)
         || ');'
         || 'END;';

      DBMS_OUTPUT.PUT_LINE (v_sql_string);
      EXECUTE IMMEDIATE v_sql_string;


   END IF;

--create job
v_job_name:='P_JOB';
v_sql_string:='SELECT COUNT(1) from user_scheduler_jobs where job_name=:1';
     EXECUTE IMMEDIATE v_sql_string
                   INTO v_count
                  USING v_job_name;
      IF v_count = 0
      THEN
         v_sql_string :=
                ' BEGIN  ' 
		|| 'DBMS_SCHEDULER.create_job (' 
		|| ' job_name        => '
		|| CHR(39) 
		|| 'P_JOB'
		|| CHR(39) 
		|| ',' 
		|| '  program_name        => '
		|| CHR(39) 
		|| 'P_PROG'
		|| CHR(39) 
		|| ',' 
		|| '  schedule_name        => '
		|| CHR(39) 
		|| 'P_SCHED'
		|| CHR(39) 
		|| ',' 
		|| 'enabled         => FALSE,'
		|| 'auto_drop       => FALSE,'
		|| 'comments        =>'
		|| CHR(39) 
		|| 'Job to execute P_PROC stored proc every day at 2 am' 
		|| CHR(39) 
		|| ');' 
		|| 'END;';
   DBMS_OUTPUT.PUT_LINE(v_sql_string);
   EXECUTE IMMEDIATE v_sql_string;


--Enable the job
      v_sql_string :=
            ' BEGIN  '
         || 'DBMS_SCHEDULER.enable    ('
         || ' name         => '
         || CHR (39)
         || 'P_JOB'
         || CHR (39)
         || ');'
         || 'END;';

      DBMS_OUTPUT.PUT_LINE (v_sql_string);
      EXECUTE IMMEDIATE v_sql_string;


      END IF;

 EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);

      raise_application_error (-20005,
                               'sqlcode = ' || SQLCODE || ' , sqlerrm = "' || SQLERRM || '"'
                              );

END;
/

select next_RUN_date from dba_scheduler_jobs where job_name='P_JOB';

10/24/2013 12:00:10.300000 AM -07:00   <--i want this to be today in next 10 minutes...how to do that?

Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599258 is a reply to message #599255] Wed, 23 October 2013 05:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
if you simply execute the procedure calls rather than wrapping them up in EXECUTE IMMEDIATE it will be a lot easier to see what is going on.
Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599259 is a reply to message #599258] Wed, 23 October 2013 06:24 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi John,
Thanks for reviewing my question. My doubt is not about the procedure itself. I have written it to ensure that it fails! My question is - I want this procedure to run today but it is showing the start date as tomorrow. How can I fix that? -it is basically the question on how do I set up a dbms_scheduler job to run in next 10 minutes today?
Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599263 is a reply to message #599259] Wed, 23 October 2013 06:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Until you write it in a sensible way, it is virtually impossible to debug. All I can see at the moment is that you are specifying bysecond=10 which is certainly wrong.
There is no reason for making your life hard by writing code like that.
Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599265 is a reply to message #599263] Wed, 23 October 2013 06:54 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi John,

This is what I specified:

         || '   start_date            => trunc(sysdate)'
          || ','
         || '  repeat_interval         => '
         || CHR (39)
         || 'freq=MINUTELY;byhour=0;byminute=0;bysecond=10'



How should I change it to make it better - I am new and not an expert so will be thankful for help.
The goal is that once I run the procedure to set up the job, it should start running in next 10 minutes.

Thanks,

icon13.gif  Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599270 is a reply to message #599265] Wed, 23 October 2013 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am new and not an expert


Quote:
Registered: December 2005


Desperate case?

Quote:
it should start running in next 10 minutes.


So maybe you have to change:

Quote:
start_date => trunc(sysdate)


Re: Making a dbms_scheduler job to execute in next 10 minutes [message #599310 is a reply to message #599270] Wed, 23 October 2013 09:53 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks experts for your reviews and helpful comments. Yes something must have been wrong the way I coded it. I changed it like this and then it went fine:

         || '   start_date            => trunc(sysdate)  '
         || ','
         || '  repeat_interval         => '
         || CHR (39)
         || 'freq=minutely;'


It was all good after this change. Thanks again to all the experts.
Previous Topic: DBMS_SERVER_ALERT tablespace full alert timing
Next Topic: Identifiying two databases involved in a distributed transaction
Goto Forum:
  


Current Time: Fri Mar 29 01:37:08 CDT 2024