Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job issue (oracle ,9i,window)
dbms_job issue [message #391444] Thu, 12 March 2009 05:05 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

I am to trying schedule the job which calls the below procedure in every second .

CREATE OR REPLACE PROCEDURE TempInsert1 AS
BEGIN
  INSERT INTO temp_table1 (num_col, char_col)
    VALUES (temp_seq.NEXTVAL,
            TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  COMMIT;
END TempInsert1;
/

declare
    my_job number;
  begin
    dbms_job.submit(job=>my_job, what=>'TempInsert1;',next_date=>sysdate,interval=>'sysdate+1/86400');
    DBMS_OUTPUT.PUT_LINE('my_job='||my_job);
  end;



but I am not getting any result in table. Could you please let me know what i need to check.


Thanks,
Sagar
Re: dbms_job issue [message #391450 is a reply to message #391444] Thu, 12 March 2009 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at the BROKEN and FAILURES columns in USER_JOBS, and have a look in the alert.log file for details of any error encountered running the job.
Re: dbms_job issue [message #391451 is a reply to message #391450] Thu, 12 March 2009 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also check the Job_queue_processes value in v$parameter - that needs to be >0 for jobs to run.

Re: dbms_job issue [message #391453 is a reply to message #391451] Thu, 12 March 2009 05:24 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Thanks for replay.

I have checked the USER_JOBS table. It is blank. I have tried to check data in v$parameter, I am getting table or view does not exist error in Scott schema

Re: dbms_job issue [message #391469 is a reply to message #391453] Thu, 12 March 2009 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then ask your DBA to check the parameter value.

Regards
Michel
Re: dbms_job issue [message #391505 is a reply to message #391469] Thu, 12 March 2009 07:05 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

I have setup same procedure in different oracle schema database.
i have checked job detail in user_job.

 select NEXT_DATE, NEXT_SEC, BROKEN, FAILURES, WHAT from USER_JOBS where JOB=913870
NEXT_DATE             NEXT_SEC BROKEN FAILURES WHAT                             
--------------------- -------- ------ -------- ---------------------------------
3/12/2009 4:49:53 AM  04:49:53 N               TempInsert;                      
1 row selected


I have not found any column name as'Job_queue_processes' in v$parameter.

Please advice what I need to check now.

Thanks,
Sagar
Re: dbms_job issue [message #391526 is a reply to message #391505] Thu, 12 March 2009 07:49 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not a column, it is a parameter name.

Regards
Michel
Previous Topic: Top records based on a column
Next Topic: trigger
Goto Forum:
  


Current Time: Fri Dec 02 17:04:17 CST 2016

Total time taken to generate the page: 0.20574 seconds