Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB.SUBMIT not appearing (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
DBMS_JOB.SUBMIT not appearing [message #684982] Mon, 04 October 2021 08:33 Go to next message
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;
/
Please help me to understand this
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 #684984 is a reply to message #684982] Mon, 04 October 2021 09:37 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Quote:
Any alternate to run the jobs at specific time interval apart from the scheduler
Why "apart from the scheduler"? dbms_scheduler IS the method to be used to schedule jobs within the oracle rdbms. It is replacing dbms_job.

This quote is from the 19c Database Administrator's Guide:

Quote:
Starting with Oracle Database 11g Release 2 (11.2), Oracle Scheduler replaces DBMS_JOB. Oracle Scheduler is more powerful and flexible than DBMS_JOB, which is a package used to schedule jobs. Although DBMS_JOB is still supported for backward compatibility, Oracle strongly recommends that you switch from DBMS_JOB to Oracle Scheduler.
Re: DBMS_JOB.SUBMIT not appearing [message #684985 is a reply to message #684984] Mon, 04 October 2021 09:44 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks EdStevens.

Will try to implement the same by using ORACLE SCHEDULER.
Re: DBMS_JOB.SUBMIT not appearing [message #684986 is a reply to message #684982] Mon, 04 October 2021 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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.
New session:
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
Note that you required the job to execute only once 30 seconds after being submitting, this may be why you didn't see it in DBA_JOBS: it has already be executed and cleaned.

Re: DBMS_JOB.SUBMIT not appearing [message #684993 is a reply to message #684987] Tue, 05 October 2021 06:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: DBMS_JOB.SUBMIT not appearing [message #685078 is a reply to message #684993] Mon, 18 October 2021 03:12 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Thanks for correcting my issue Michel. Will try to implement the same .
Previous Topic: Select Query suggestion
Next Topic: Prefix data into an existing clob
Goto Forum:
  


Current Time: Thu Mar 28 18:05:30 CDT 2024