PL/SQL DBMS_JOB.WHAT

The DBMS_JOB.WHAT procedure in Oracle is used to retrieve information about a specific job in the job queue. It provides details about the job’s status, execution schedule, and other attributes. However, it’s important to note that DBMS_JOB is considered deprecated and has been superseded by the more powerful and feature-rich DBMS_SCHEDULER package.

Overview of DBMS_JOB.WHAT

Here’s a breakdown of the DBMS_JOB.WHAT procedure:

Purpose

Retrieve information about a specific job in the job queue.
Provides details like job status, next execution time, interval, job class, and more.

Arguments

job_id: The unique identifier of the job you want information about.

Return value

None. Information about the job is populated into various output variables:

job_owner: Owner of the job.
job_name: Name of the job.
job_enabled: Whether the job is currently enabled or disabled.
job_class: Job class assigned to the job.
broken: Whether the job is currently in a broken state.
next_date: Next scheduled execution date/time.
interval: Interval between job executions (e.g., minutes, hours).
job_action: Details about the action the job performs (e.g., PL/SQL block, stored procedure call).
Other variables for additional information.

Example

Example Usage:

DECLARE
  job_number NUMBER := 1234;
  job_owner VARCHAR2(30);
  job_name VARCHAR2(30);
  -- Other variables for remaining output parameters
BEGIN
  DBMS_JOB.WHAT(job_number, job_owner, job_name, ...);
  
  IF enabled THEN
    DBMS_OUTPUT.PUT_LINE('Job ' || job_number || ' is owned by ' || job_owner || ' and named ' || job_name);
    -- Use other retrieved information as needed
  ELSE
    DBMS_OUTPUT.PUT_LINE('Job ' || job_number || ' is disabled.');
  END IF;
END;
/

Important points

DBMS_JOB is deprecated and may be removed in future Oracle Database releases.
Consider migrating your job scheduling to the DBMS_SCHEDULER package for better features and support.