| DBMS_JOB.SUBMIT not appearing [message #684982] |
Mon, 04 October 2021 08:33  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi All
In earlier versions (Oracle 11) we used to work with DBMS_JOB.Submit efficiently .
But another database with version 19C after submitting the jobs, we are unable to see the entries in DBA_JOBS & its not trigered.
Following is code snippet ;
Declare
job_id binary_integer;
begin
dbms_job.submit(job=>job_id,what=>'ALTER PROCEDURE HR.PROC1 COMPILE;',next_date=>sysdate + 30/(24*60*60));
commit;
end;
/
Any alternate to run the jobs at specific time interval apart from the scheduler
Thanks in advance
SaiPradyumn
|
|
|
|
|
|
|
|
| Re: DBMS_JOB.SUBMIT not appearing [message #684986 is a reply to message #684982] |
Mon, 04 October 2021 10:38  |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
DBMS_JOB jobs require you commit after submitting them this may explain you didn't see them in DBA_JOBS and the job is not submitted.
[Update: Ooops, I didn't see the "commit" in your PL/SQL block.]
I disagree with Ed about old and new jobs, I still think and maintain that these different jobs have different purposes.
For instance, as I said, DBMS_JOB jobs needs to be committed after submission when DBMS_SCHEDULER is auto-commit. So submitting a job can be part of your transaction with the former when it is not with the later. his means the original jobs are not created if your transaction aborts and is rolled back when it requires added code for the new jobs.
Read our wiki pages DBMS_JOB and DBMS_SCHEDULER.
[Updated on: Mon, 04 October 2021 10:39] Report message to a moderator |
|
|
|
| Re: DBMS_JOB.SUBMIT not appearing [message #684987 is a reply to message #684982] |
Mon, 04 October 2021 10:51 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I tested your code, it works for me:
SQL> Declare
2 job_id binary_integer;
3 begin
4 dbms_job.submit(job=>job_id,what=>'ALTER PROCEDURE HR.PROC1 COMPILE;',next_date=>sysdate + 30/(24*60*60));
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
conn michel/michel
Connected.
SQL> select job,what,next_date,interval from dba_jobs where what like '%HR.PROC1%';
JOB
----------
WHAT
------------------------------------------------------------------------------------------------------------------------
NEXT_DATE
-------------------
INTERVAL
------------------------------------------------------------------------------------------------------------------------
22
ALTER PROCEDURE HR.PROC1 COMPILE;
04/10/2021 17:47:09
1 row selected.
SQL> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.
SQL> select job,what,next_date,interval from dba_jobs where what like '%HR.PROC1%';
no rows selected
SQL> @v
Oracle version: 19.12.0.0.210720 EE - JVM v1.8.0_301 - timezone files v32
|
|
|
|
| Re: DBMS_JOB.SUBMIT not appearing [message #684993 is a reply to message #684987] |
Tue, 05 October 2021 06:58 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Yes, it will work fine but DBMS_JOB in 19C is just "illusion" (a wrapper to DBMS_SCHEDULER). Check DBA_SCHEDULER_JOBS and you should see DBMS_JOB$_xxxxxx where xxxxxx is DBMS_JOB job number.
SY.
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
