No question at this time
DBA Top 10
1 M. Cadot 6600
2 B. Vroman 3400
3 J. PĂ©ran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 900
6 P. Wisse 600
7 T. Boles 400
7 D. Walgude 400
7 D. Johnson 400
10 J. Alcroft 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48675
Total active users1329
Act. users last 24h2
Act. users last hour0
Registered user hits last week28
Registered user hits last month451
Go up

DBMS_JOB EXECUTION
Next thread: DATA CONVERSION
Prev thread: How to speed up the loading process

Message Score Author Date
Hi All, I scheduled to execute a procedure to c...... BASHYAM RAJA May 05, 2005, 11:27
It looks like it is executing but not commiting. ...... Stacey Jones May 05, 2005, 14:58
Whoops, left the end off, should look like this: ...... Stacey Jones May 05, 2005, 15:16
Here is what I did. It's little different than you...... Zuber Shethwala May 05, 2005, 22:04

Follow up by mail Click here


Subject: DBMS_JOB EXECUTION
Author: BASHYAM RAJA, Bahrain
Date: May 05, 2005, 11:27, 6266 days ago
Os info: WINDOWS 2000 32 BITS
Oracle info: Oracle9i Enterprise Edition Release 9.2.0.6.0
Message: Hi All,

I scheduled to execute a procedure to create & update statics for every 8 hours,
ie.
declare
l_job number;
P_DUTY VARCHAR2(2) := 'ZZ';
P_NIGHT_DUTY VARCHAR2(1) := 'N';
begin
dbms_job.submit( l_job,
' FS_DOM_REP_AIRLINE_MOVEMENTS( SYSDATE , ''' || P_DUTY || ''' );
FS_DOM_REP_AIRLINE_NIGHT_SHIFT( SYSDATE , ''' || P_NIGHT_DUTY || ''' );'
,trunc(sysdate), 'sysdate+8/24' );
commit;
END;

When i query User_Jobs, only Last_date,Last_sec,
and Next_date,next_sec columns updated, but Total_Time=0,Failures=0.

05-MAY-05 07:24:20 Last_date,Last_sec
05-MAY-05 15:24:20 Next_date,Next_sec

Records are not inserted / updated thru dbms_jobs.

Why the procedure is not executing.

Our Init Parameter setting

job_queue_processes = 10

Already 4 jobs running with single parameters(Passsing Sysdate), this job i'm passing 2 parameters(Sysdate,char value).

Regards,
Raja




Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBMS_JOB EXECUTION
Author: Stacey Jones, United States
Date: May 05, 2005, 14:58, 6266 days ago
Message: It looks like it is executing but not commiting. Try adding a commit in the work you give the job. For example:

'BEGIN
FS_DOM_REP_AIRLINE_MOVEMENTS( SYSDATE , ''' || P_DUTY || ''' );
FS_DOM_REP_AIRLINE_NIGHT_SHIFT( SYSDATE , ''' || P_NIGHT_DUTY || ''' );
COMMIT;
'
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBMS_JOB EXECUTION
Author: Stacey Jones, United States
Date: May 05, 2005, 15:16, 6266 days ago
Message: Whoops, left the end off, should look like this:
'BEGIN
FS_DOM_REP_AIRLINE_MOVEMENTS( SYSDATE , ''' || P_DUTY || ''' );
FS_DOM_REP_AIRLINE_NIGHT_SHIFT( SYSDATE , ''' || P_NIGHT_DUTY || ''' );
COMMIT;
END;
'
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBMS_JOB EXECUTION
Author: Zuber Shethwala, United States
Date: May 05, 2005, 22:04, 6266 days ago
Message: Here is what I did. It's little different than yours, but it does work. Only difference you find is in the date (input). You use Sysdate where as I have defined actual date and time. Using this I was able to run every ten minutes. You may want to change it to 8/24 in your case.

create or replace procedure FS_DOM_REP_AIRLINE_MOVEMENTS (
p_date IN Date,
p_duty IN Varchar2
) AS

Begin
dbms_output.put_line('Movements : IT WORKS ');
End;
/

create or replace procedure FS_DOM_REP_AIRLINE_NIGHT_SHIFT (
p_date IN Date,
p_duty IN Varchar2
) AS
Begin
dbms_output.put_line('Night Shift : IT WORKS ');
End;
/

declare
l_job number;
P_DUTY VARCHAR2(2) := 'ZZ';
P_NIGHT_DUTY VARCHAR2(1) := 'N';
begin
dbms_job.submit( l_job,
' FS_DOM_REP_AIRLINE_MOVEMENTS( SYSDATE , ''' || P_DUTY || ''' );
FS_DOM_REP_AIRLINE_NIGHT_SHIFT( SYSDATE , ''' || P_NIGHT_DUTY || ''' );'
,to_date('05-MAY-2005 14:40:00','DD-MON-YYYY HH24:MI:SS'), 'sysdate + 10/1440' );
commit;
dbms_output.put_line ('Job is ' || l_job);
END;
/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here