Skip to Main Content
  • Questions
  • Get log id of running job in package

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prakash.

Asked: June 24, 2023 - 9:27 am UTC

Last updated: June 30, 2023 - 2:40 pm UTC

Version: 19.0.0.0

Viewed 1000+ times

You Asked

Hi ,

I want to dbms_job log id in the package from ALL_SCHEDULER_RUNNING_JOB in a package to monitor the jobs.
Details job log for which running jobs.


We have a procedure which writes the data from ALL_SCHEDULER_RUNNING_JOB ,

Code block is as follow.

DECLARE  
XXTAX_JOB_LOG_ID  VARCHAR2(30);  
XXTAX_JOB_NAME           VARCHAR2(30);
BEGIN  
SELECT LOG_ID, JOB_NAME  INTO   XXTAX_JOB_LOG_ID , XXTAX_JOB_NAME FROM ALL_SCHEDULER_RUNNING_JOBS  WHERE  SESSION_ID = SYS_CONTEXT('USERENV','SID')  
  AND RUNNING_INSTANCE = SYS_CONTEXT('USERENV','INSTANCE');    
END;


Regards
Prakash

and Chris said...

I'm unsure what you're trying to do here. The query uses the current session ID, so only returns a row if you call it from within the job itself. i.e. you'll have to update the job action to include this.

Even if you do, it tells you relatively little. ALL_SCHEDULER_RUNNING_JOBS.LOG_ID holds the "SUCCEEDED" Log ID that will get created when the job completes. If you're trying to see what a job is doing NOW, it's of no use.

If you want to check the progress of active jobs, add logging to the code in the job itself. This will insert details of progress into a table. Logger is a good package to use if you don't already have something in place:

https://github.com/OraOpenSource/Logger

If this is not what you want, please clarify what you're trying to do.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sailesh, June 30, 2023 - 3:37 am UTC

I think Prakash is looking for other options to get current job Id in the dbms procedure.
I know the code he has written is not correct, but do we have any other way to get current dbms job id inside the procedure which dbms job was based on?
Chris Saxon
June 30, 2023 - 2:40 pm UTC

Honestly: I don't understand what Prakash is looking for.

If they do want the "job id" there's a simple answer:

You can't get it!

From 19c all dbms_jobs use the scheduler under the covers. There is no job ID for these:

https://mikedietrichde.com/2019/05/24/dbms_job-behavior-change-in-oracle-19c-during-upgrade/

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database