Home » SQL & PL/SQL » SQL & PL/SQL » Scheduling jobs (Oracle IDS 10g,Windows XP)
Scheduling jobs [message #400622] Wed, 29 April 2009 02:33 Go to next message
anugraha
Messages: 15
Registered: February 2009
Junior Member
Hi All !

I need to run a procedure every six months that is on 1stjan and 1stjuly ..

How can i schedule this ?

Pls help

Thanks and Regards
anu
Re: Scheduling jobs [message #400624 is a reply to message #400622] Wed, 29 April 2009 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using dbms_job or dbms_scheduler for instance.

Regards
Michel
Re: Scheduling jobs [message #400631 is a reply to message #400624] Wed, 29 April 2009 03:00 Go to previous messageGo to next message
anugraha
Messages: 15
Registered: February 2009
Junior Member
S .. i already tried with the following coding
[ALIGN=left]declare
      l_job number;
   begin
      dbms_job.submit( l_job,
                  ' begin earn_bal_up(''Earn Leave''); end;',
                       add_months(trunc(sysdate,'YEAR'),12));
  end;[/ALIGN]


And this works for 1st jan .. But iam not sure how should i give for 1st july.. i tried with these in sql window
1* select add_months(trunc(sysdate, 'YEAR'),12) from dual
  2  / 
 
ADD_MONTH
---------
01-JAN-10
 
SQL> select add_months(trunc(sysdate, 'YEAR'),6
  2  ) from dual;
 
ADD_MONTH
---------
01-JUL-09
 
SQL> select add_months(trunc(sysdate, 'YEAR'),18 ) from dual;
 
ADD_MONTH
---------
01-JUL-10


So, which is correct for scheduling on july 1st?

help pls ..

Thanks and Regards
anu
Re: Scheduling jobs [message #400632 is a reply to message #400624] Wed, 29 April 2009 03:01 Go to previous messageGo to next message
anugraha
Messages: 15
Registered: February 2009
Junior Member
S .. i already tried with the following coding
[ALIGN=left]declare
      l_job number;
   begin
      dbms_job.submit( l_job,
                  ' begin earn_bal_up(''Earn Leave''); end;',
                       add_months(trunc(sysdate,'YEAR'),12));
  end;[/ALIGN]


And this works for 1st jan .. But iam not sure how should i give for 1st july.. i tried with these in sql window
1* select add_months(trunc(sysdate, 'YEAR'),12) from dual
  2  / 
 
ADD_MONTH
---------
01-JAN-10
 
SQL> select add_months(trunc(sysdate, 'YEAR'),6
  2  ) from dual;
 
ADD_MONTH
---------
01-JUL-09
 
SQL> select add_months(trunc(sysdate, 'YEAR'),18 ) from dual;
 
ADD_MONTH
---------
01-JUL-10


So, which is correct for scheduling on july 1st every year?

help pls ..

Thanks and Regards
anu
Re: Scheduling jobs [message #400658 is a reply to message #400632] Wed, 29 April 2009 04:11 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select to_date('01/01/2009','DD/MM/YYYY') dat from dual
  4      union all
  5      select to_date('01/07/2009','DD/MM/YYYY') dat from dual
  6    )
  7  select dat, 
  8         add_months(trunc(sysdate,'YEAR'),
  9                    case when dat < add_months(trunc(sysdate,'YEAR'),6) then 6
 10                         else 12 
 11                    end) nextone
 12  from data
 13  /
DAT         NEXTONE
----------- -----------
01-JAN-2009 01-JUL-2009
01-JUL-2009 01-JAN-2010

2 rows selected.

Regards
Michel
Previous Topic: drop oldest partition
Next Topic: function to convert alphanumeric character to number
Goto Forum:
  


Current Time: Fri Dec 09 07:43:39 CST 2016

Total time taken to generate the page: 0.16061 seconds