Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB.SUMBIT not working
DBMS_JOB.SUMBIT not working [message #415768] Wed, 29 July 2009 04:23 Go to next message
shekhar.salunkhe
Messages: 154
Registered: January 2008
Location: Pune
Senior Member
Dear All,
I have created one job which is to be executed every 3 minutes.
Begin
  dbms_job.submit(njob_no,'PROC_INT;',sysdate,'sysdate+(3/24860)');
end;

Using this job is successfully created and it record is dispalyed in USER_JOBS table, but the effect of execution of procedure PROC_INT is not displayed.
Can any one help me out
Regards,
Shekhar

[EDITED by LF: fixed topic title typo (was "noy working"); applied [code] tags]

[Updated on: Wed, 29 July 2009 05:54] by Moderator

Report message to a moderator

Re: DBMS_JOB.SUMBIT noy working [message #415771 is a reply to message #415768] Wed, 29 July 2009 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
have you issued a commit after the call to dbms_job?
Re: DBMS_JOB.SUMBIT noy working [message #415772 is a reply to message #415768] Wed, 29 July 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not explain what you did, copy and paste it.
What you posted DO NOT WORK.
SQL> Begin
  2  dbms_job.submit(njob_no,'PROC_INT;',sysdate,'sysdate+(3/24860)');
  3  end;
  4  /
dbms_job.submit(njob_no,'PROC_INT;',sysdate,'sysdate+(3/24860)');
                *
ERROR at line 2:
ORA-06550: line 2, column 17:
PLS-00201: identifier 'NJOB_NO' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: DBMS_JOB.SUMBIT noy working [message #415776 is a reply to message #415772] Wed, 29 July 2009 04:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, why do you want to run the job every 3 minutes or every 10 seconds?

SQL> ALTER session SET nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> SELECT SYSDATE, sysdate+(3/24860) FROM dual;

SYSDATE             SYSDATE+(3/24860)
------------------- -------------------
29.07.2009 11:45:05 29.07.2009 11:45:15

SQL>
Re: DBMS_JOB.SUMBIT not working [message #415823 is a reply to message #415768] Wed, 29 July 2009 08:04 Go to previous messageGo to next message
shekhar.salunkhe
Messages: 154
Registered: January 2008
Location: Pune
Senior Member
My Code is like This
SQL> 
    Declare 
       Njob_no number;
    Begin
  2  dbms_job.submit(njob_no,'proc_int;',sysdate,'sysdate+(3/24860)');
  3  end;
  4  /


I have commited after this code and record for this job is shown in USER_JOBS table. But is not executed automatically,
I have to executed it using
   executed dbms.job_run(job_no);


How it can be automatically executed.
Re: DBMS_JOB.SUMBIT not working [message #415825 is a reply to message #415768] Wed, 29 July 2009 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
what does user_jobs show for that job?
Re: DBMS_JOB.SUMBIT not working [message #415826 is a reply to message #415825] Wed, 29 July 2009 08:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can also check the things I checked when I had a similar problem a while ago:

http://www.orafaq.com/forum/t/80780/0/

In my case it was ultimately resolved by restarting the instance.
Re: DBMS_JOB.SUMBIT not working [message #415829 is a reply to message #415823] Wed, 29 July 2009 08:20 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check job_queue_processes parameter.

Regards
Michel
Previous Topic: Taking more time to execute query
Next Topic: how to select all packages
Goto Forum:
  


Current Time: Sat Dec 03 08:21:14 CST 2016

Total time taken to generate the page: 0.09495 seconds