Home » SQL & PL/SQL » SQL & PL/SQL » Logic help required (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Logic help required [message #332583] Wed, 09 July 2008 01:33 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Here are my requirements from my client. Please guide me as to how to go about step by step.

1. I have a job which has to be scheduled to run at the start of every month manually as shown below:

PROCEDURE     EOM_EXECUTE
   IS

n_job    binary_integer;
v_date  date := to_date('01-JUN-2008 00:01','dd-mon-yyyy hh24:mi');
n_proc   varchar2(32) := 'adm.EOM_STREAMLINE;';
v_msg varchar(32);

BEGIN

 dbms_job.submit(n_job, n_proc, v_date);

    COMMIT;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    v_msg := 'EOM_EXECUTE: Error: ' || SQLERRM;
    dbms_output.put_line(v_msg);
END EOM_EXECUTE;


2. My requirement is that I should write a procedure to automate to run this job 'adm.EOM_STREAMLINE;' at the 1st of every month at exactly 00:01 (AM) for the next 10 years

3. This is my requirement, Please can anyone guide me in a structured manner which is the best way to accomplish this task

[Updated on: Wed, 09 July 2008 01:45]

Report message to a moderator

Re: Logic help required [message #332589 is a reply to message #332583] Wed, 09 July 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think you can use Oracle 9.2 for the next 10 years.
So it is not necessary to put an end time.
Now the first day of the month is given by "trunc(date,'MONTH')", then:
- 1 day = 1
- 1 hour = 1/24
- 1 minute = 1/1440

Regards
Michel
Re: Logic help required [message #332591 is a reply to message #332589] Wed, 09 July 2008 02:04 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
thanks for the tip, I shall just have lunch n be back in 45 minutes !!
Re: Logic help required [message #332592 is a reply to message #332583] Wed, 09 July 2008 02:04 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
begin
  dbms_scheduler.create_job(
      job_name => 'DEMO_JOB_SCHEDULE'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin adm.EOM_STREAMLINE; end; '
     ,start_date => '01/06/2008 00:01 AM'
     ,repeat_interval => 'repeat_interval => 'FREQ=MONTHLY''
     ,enabled => TRUE
     ,comments => 'Demo for job schedule.');
end;
/

begin
dbms_job.submit(
   what=>'adm.EOM_STREAMLINE; ', 
   next_date=>TRUNC(SYSDATE+ 1), 
   interval=>ADD_MONTHS(SYSDATE, 1));  
end;
/
Re: Logic help required [message #332596 is a reply to message #332583] Wed, 09 July 2008 02:12 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
this will also do the same.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SP'
     ,next_date  => to_date('09/07/2008 12:40:57','dd/mm/yyyy hh24:mi:ss')
     ,[B]interval   => 'TRUNC(LAST_DAY(SYSDATE)) + 1'[/B]
     ,no_parse   => FALSE
    );
:JobNumber := to_char(X);
END;
Re: Logic help required [message #332602 is a reply to message #332592] Wed, 09 July 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora_2007 wrote on Wed, 09 July 2008 09:04
begin
  dbms_scheduler.create_job(
      job_name => 'DEMO_JOB_SCHEDULE'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin adm.EOM_STREAMLINE; end; '
     ,start_date => '01/06/2008 00:01 AM'
     ,repeat_interval => 'repeat_interval => 'FREQ=MONTHLY''
     ,enabled => TRUE
     ,comments => 'Demo for job schedule.');
end;
/

begin
dbms_job.submit(
   what=>'adm.EOM_STREAMLINE; ', 
   next_date=>TRUNC(SYSDATE+ 1), 
   interval=>ADD_MONTHS(SYSDATE, 1));  
end;
/


dbms_scheduler does not exists in 9i.
Neither your next_date nor interval satifies the requirements.

Regards
Michel

[Updated on: Wed, 09 July 2008 02:36]

Report message to a moderator

Re: Logic help required [message #332604 is a reply to message #332596] Wed, 09 July 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adit_me1 wrote on Wed, 09 July 2008 09:12
this will also do the same.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SP'
     ,next_date  => to_date('09/07/2008 12:40:57','dd/mm/yyyy hh24:mi:ss')
     ,[B]interval   => 'TRUNC(LAST_DAY(SYSDATE)) + 1'[/B]
     ,no_parse   => FALSE
    );
:JobNumber := to_char(X);
END;


It miss the date by 1 minute.

Regards
Michel

Re: Logic help required [message #332621 is a reply to message #332604] Wed, 09 July 2008 03:51 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hi guys m back

Michel could you please tell me how
Quote:
It miss the date by 1 minute ?.
Re: Logic help required [message #332626 is a reply to message #332621] Wed, 09 July 2008 03:57 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Yes,
Michel is right. This will schedule the job to run on the midnight of the 1st day of everymonth. So that makes it the 2nd of every monnth. You can subtract 1 minute from it by giving

interval   => 'TRUNC(LAST_DAY(SYSDATE)) + 1 - 1/24/60'
Re: Logic help required [message #332627 is a reply to message #332626] Wed, 09 July 2008 03:58 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
hope i am right Embarassed
Re: Logic help required [message #332633 is a reply to message #332627] Wed, 09 July 2008 04:11 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
i guess it was suppose to be

select TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24/60 from dual


am i right ?

TRUNC(LAST_DAY(SYSDATE))+1+1/24/60
----------------------------------
              8/1/2008 12:01:00 AM 

[Updated on: Wed, 09 July 2008 04:14]

Report message to a moderator

Re: Logic help required [message #332635 is a reply to message #332621] Wed, 09 July 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jagannathkiran wrote on Wed, 09 July 2008 10:51
hi guys m back

Michel could you please tell me how
Quote:
It miss the date by 1 minute ?.


Quote:
at the 1st of every month at exactly 00:01

You date is not at "exactly 00:01".

Regards
Michel

[Ooops! between I click on "Reply", write my answer and click on "Submit Reply" this have already been answered]

[Updated on: Wed, 09 July 2008 04:16]

Report message to a moderator

Re: Logic help required [message #332637 is a reply to message #332635] Wed, 09 July 2008 04:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
select TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24/60


so is the above right for the interval ?

Will it run at 00:01 if i give the interval as

trunc(sysdate)

[Updated on: Wed, 09 July 2008 04:43]

Report message to a moderator

Re: Logic help required [message #332640 is a reply to message #332633] Wed, 09 July 2008 04:53 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
YES
Re: Logic help required [message #332642 is a reply to message #332640] Wed, 09 July 2008 04:54 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
select TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24/60


will make it run at 00:01 hrs of the 1st day of every month.
Re: Logic help required [message #332643 is a reply to message #332637] Wed, 09 July 2008 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24/60

This indeed gives the correct interval.

Regards
Michel
DBMS_JOB.SUBMIT [message #332647 is a reply to message #332643] Wed, 09 July 2008 04:59 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I created this test procedure

PROCEDURE TEST
   IS
BEGIN
dbms_output.put_line('This is suppose to run on the 1st of every month ');
EXCEPTION WHEN others THEN
    dbms_output.put_line('Error in test '||SQLERRM);
END;


Then i wrote this block, but i got this error while running it, PLEASE see below

declare
jobno integer;
begin
dbms_job.submit(:jobno,'JAGANNK1.TEST',
                TO_DATE('09-Jul-2008 05:39:00','dd-mon-yyyy hh24:mi:ss'),
                TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24/60);
end;   


Error i got is

3:28:17 PM  ORA-23319: parameter value "01-Aug-2008" is not appropriate
3:28:17 PM  ORA-06512: at "SYS.DBMS_JOB", line 57
3:28:17 PM  ORA-06512: at "SYS.DBMS_JOB", line 134
3:28:17 PM  ORA-06512: at line 4

[Updated on: Wed, 09 July 2008 05:25]

Report message to a moderator

Re: DBMS_JOB.SUBMIT [message #332670 is a reply to message #332647] Wed, 09 July 2008 06:01 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
guess proper formatting is required.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SP'
     ,next_date  => to_date('09/07/2008 12:40:57','dd/mm/yyyy hh24:mi:ss')
     ,[B]interval   => 'TRUNC(LAST_DAY(SYSDATE)) + 1+1/24/60'[/B]
     ,no_parse   => FALSE
    );
:JobNumber := to_char(X);
END;
Re: DBMS_JOB.SUBMIT [message #332680 is a reply to message #332647] Wed, 09 July 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In other words, INTERVAL parameter is a STRING not a DATE.

Regards
Michel
Re: DBMS_JOB.SUBMIT [message #332856 is a reply to message #332680] Wed, 09 July 2008 23:36 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi michel, thanks for the help, I got another critical task at hand, so i shall get back to this topic ASAP, thanks Smile
Previous Topic: Difference on Delete
Next Topic: About Table Structure
Goto Forum:
  


Current Time: Fri Dec 09 01:51:42 CST 2016

Total time taken to generate the page: 0.10802 seconds