Home » SQL & PL/SQL » SQL & PL/SQL » Job scheduling (Oracle 9i)
Job scheduling [message #425641] Sun, 11 October 2009 03:10 Go to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Hi,

I am not able to schedule the job. Followed the following procedures. Kindly help me out.

1) Created a precedure as follows:
create procedure test_1 as
begin
update test set sal = sal+15;
end;
/
Output:
Procedure created.

2)Submiting the job - I want the job to be run immediately

declare jobno number = 21;
begin
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
end;
/
Output:
PL/SQL procedure sucessfully completed.

Please let me know whether i need to setup anything before running the above procedures...
Re: Job scheduling [message #425642 is a reply to message #425641] Sun, 11 October 2009 03:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Only set the job_queue_processes parameter to something other than 0, so that there are actually job queue processes running.

And commit after you submitted the job, so that the job queue processes can actually see the job.

[Updated on: Sun, 11 October 2009 03:30]

Report message to a moderator

Re: Job scheduling [message #425653 is a reply to message #425642] Sun, 11 October 2009 04:36 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Thanks for the solution...its working....

I want to schedule the job i.e run the job automatically every 3 hours .

How can i tune the above in
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
Re: Job scheduling [message #425656 is a reply to message #425642] Sun, 11 October 2009 05:04 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
The jobs are running fine but the tables are getting updated abruptly

The procedures states "update table set sal = sal + 15"

I expect the sal of all the employees to be incremented to 15, but when i run the job the sal for all the empl are incremented to 13115

Query before the job is submitted


SQL> select * from test;

EMP EXP SAL
---------- ------------ ----------
488 5 2236060
1245 4 1835860
21 5 2135860
34 6 2935860
34 3 2935860
123 7 2135860
57 6 6295860

7 rows selected.

Query after the job is submitted

SQL> select * from test;

EMP EXP SAL
---------- ----------- ---------
488 5 2249175
1245 4 1848975
21 5 2148975
34 6 2948975
34 3 2948975
123 7 2148975
57 6 6308975

[Updated on: Sun, 11 October 2009 05:06]

Report message to a moderator

Re: Job scheduling [message #425659 is a reply to message #425653] Sun, 11 October 2009 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Exactly 3 hours or about 3 hours?

Regards
Michel
Re: Job scheduling [message #425661 is a reply to message #425659] Sun, 11 October 2009 05:40 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
jobs needs to run every 3 hours automatically

[Updated on: Sun, 11 October 2009 05:50]

Report message to a moderator

Re: Job scheduling [message #425663 is a reply to message #425661] Sun, 11 October 2009 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Exactly 3 hours or about 3 hours?

Regards
Michel
Re: Job scheduling [message #425664 is a reply to message #425663] Sun, 11 October 2009 06:32 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
exactly 3 hours
Re: Job scheduling [message #425665 is a reply to message #425664] Sun, 11 October 2009 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which ones?

Regards
Michel
Re: Job scheduling [message #425666 is a reply to message #425665] Sun, 11 October 2009 07:00 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Hi,

sorry am not sure wat you are referring to....
i want the below job to be scheduled were the job would run for every 3 hours automatically.

declare jobno number;
begin
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
end;
/

[Updated on: Sun, 11 October 2009 07:43]

Report message to a moderator

Re: Job scheduling [message #425669 is a reply to message #425642] Sun, 11 October 2009 07:48 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Hi,
The below peice of code is getting executed, i am not able to identify the job no assigned to it. Can you pls help me out on this....

declare jobno number;
begin
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
end;
/
Re: Job scheduling [message #425671 is a reply to message #425666] Sun, 11 October 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji_cg wrote on Sun, 11 October 2009 14:00
Hi,

sorry am not sure wat you are referring to....
i want the below job to be scheduled were the job would run for every 3 hours automatically.

declare jobno number;
begin
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
end;
/

You say want the job to be executed exactly eahc 3 hours, which hours are they?
0,3,6,9...
1,4,7,10...
1h10,4h10,7h10...
Which ones? What is the starting point?

Regards
Michel

Re: Job scheduling [message #425672 is a reply to message #425669] Sun, 11 October 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji_cg wrote on Sun, 11 October 2009 14:48
Hi,
The below peice of code is getting executed, i am not able to identify the job no assigned to it. Can you pls help me out on this....

declare jobno number;
begin
dbms_job.submit( jobno,'begin test_1; end;',sysdate);
end;
/

Just use dbms_output to display it, for instance.

Regards
Michel

Re: Job scheduling [message #425678 is a reply to message #425671] Sun, 11 October 2009 11:04 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
i want the jobs to be executed at 0,3,6,9
Re: Job scheduling [message #425679 is a reply to message #425678] Sun, 11 October 2009 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
balaji_cg wrote on Sun, 11 October 2009 09:04
i want the jobs to be executed at 0,3,6,9


Please proceed to do so.
Nobody here here is stopping you from your desired goal.

Re: Job scheduling [message #425680 is a reply to message #425678] Sun, 11 October 2009 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji_cg wrote on Sun, 11 October 2009 18:04
i want the jobs to be executed at 0,3,6,9

SQL> with data as (select level-1 hour from dual connect by level <= 24)
  2  select hour, 3*(trunc(hour/3)+1) from data
  3  /
      HOUR 3*(TRUNC(HOUR/3)+1)
---------- -------------------
         0                   3
         1                   3
         2                   3
         3                   6
         4                   6
         5                   6
         6                   9
         7                   9
         8                   9
         9                  12
        10                  12
        11                  12
        12                  15
        13                  15
        14                  15
        15                  18
        16                  18
        17                  18
        18                  21
        19                  21
        20                  21
        21                  24
        22                  24
        23                  24

24 rows selected.

SQL> select to_char(sysdate,'HH24') current_hour from dual;
CU
--
18

1 row selected.

Regards
Michel

[Updated on: Sun, 11 October 2009 11:48]

Report message to a moderator

Re: Job scheduling [message #425777 is a reply to message #425679] Mon, 12 October 2009 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
balaji_cg wrote on Sun, 11 October 2009 09:04
i want the jobs to be executed at 0,3,6,9


Please proceed to do so.
Nobody here here is stopping you from your desired goal.


@MuckyDuck I see you're continuing your policy of minimally helpful postsw - good to see some things are unchanging in this chaotic world.

To see what job number your job has been created as, siply query USER/ALL/DBA_JOBS.

What you need to do to make your job repeat at a three hourly is to specify the INTERVAL parameter in your call to DBMS_JOB.SUBMIT.

INTERVAL must be an expression that evaluates to the next time that you need the job to run - so you need to work out how to use @Michels code to create a date that is the next time that this job needs to run.

Have a try and get back to us if you have any trouble.
Re: Job scheduling [message #425905 is a reply to message #425777] Tue, 13 October 2009 00:38 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Hi,

I am facing some problem is scheduling the below job:

declare jobno number;
begin
dbms_job.submit ( jobno, 'begin test1; end;',('11:30 10/13/09','HH24:MI MM/DD/YY'),'sysdate+1');
dbms_output.put_line (jobno);
commit;
end;

Output:
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'SUBMIT'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
Re: Job scheduling [message #425906 is a reply to message #425905] Tue, 13 October 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"to_date" is missing and your initerval does not fit your requirment.

Regards
Michel
Re: Job scheduling [message #425910 is a reply to message #425906] Tue, 13 October 2009 01:04 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Hi Michel,

I guess the 'to date' is for the above peice of code is
'11:30 10/13/09','HH24:MI MM/DD/YY'

And my requirement is to run the job at 11:30 on a daily basis

[Updated on: Tue, 13 October 2009 01:06]

Report message to a moderator

Re: Job scheduling [message #425913 is a reply to message #425910] Tue, 13 October 2009 01:25 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you need to pass the parameter as
to_date('11:30 10/13/09','HH24:MI MM/DD/YY')

to run every day at 11:30 change the last parameter like this

'trunc(sysdate)+1+11/24+30/24/60'





XE@SQL> variable job number;

XE@SQL> exec dbms_job.submit(:job,'begin null; end;',to_date('11:30 10/13/09','H
H24:MI MM/DD/YY'),'trunc(sysdate)+1+11/24+30/24/60');

PL/SQL procedure successfully completed.

XE@SQL> commit;

Commit complete.

XE@SQL> print job

       JOB
----------
        63

XE@SQL> select job,what,next_date from user_jobs where job = 63;

       JOB WHAT                           NEXT_DATE
---------- ------------------------------ --------------------
        63 begin null; end;               14-Oct-2009 11:30:00

1 row selected.

XE@SQL>



[Updated on: Tue, 13 October 2009 01:26]

Report message to a moderator

Re: Job scheduling [message #425922 is a reply to message #425910] Tue, 13 October 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji_cg wrote on Tue, 13 October 2009 08:04
Hi Michel,

I guess the 'to date' is for the above peice of code is
'11:30 10/13/09','HH24:MI MM/DD/YY'

And my requirement is to run the job at 11:30 on a daily basis

So it is no more 3 hours at 0,3,6,9...

Regards
Michel

Re: Job scheduling [message #425923 is a reply to message #425913] Tue, 13 October 2009 02:24 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Thanx bonker It's working....

[Updated on: Tue, 13 October 2009 02:25]

Report message to a moderator

Re: Job scheduling [message #425931 is a reply to message #425922] Tue, 13 October 2009 04:00 Go to previous messageGo to next message
balaji_cg
Messages: 22
Registered: October 2009
Junior Member
Michel,

This is new requirement....
Re: Job scheduling [message #425938 is a reply to message #425931] Tue, 13 October 2009 04:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Then you should probably start a new thread.

Seriously - how are we meant to know you're talking about a new requirement if you don't tell us.

A simpler way to write the interval is
'trunc(sysdate)+1+11.5/24'
Previous Topic: truncate via different schema owner
Next Topic: merging of rows based on column value
Goto Forum:
  


Current Time: Sun Dec 04 22:46:09 CST 2016

Total time taken to generate the page: 0.12560 seconds