Get job name inside DBMS_SCHEDULER-executed proc

An Oracle Database developer* contacted me recently with this problem:

Do you know a way to get the DBMS_SCHEDULER Job Name from within the code that is being executed by the job?

I could have told him to visit the OTN SQL and PL/SQL Forum and post his question there, but I thought that instead I would ask the players at the PL/SQL Challenge if they had any ideas. So I posted this message in the Recent News section:

Solution from Niels Hecker

-- the user needs the following privileges directly assigned:
--     EXECUTE on package DBMS_Lock
--     SELECT ANY DICTIONARY
--     CREATE JOB

------------------------------------------------------------------
-- create logging-table with associated log-procedure
CREATE TABLE tbl_LogMsg (
    ID     INTEGER,
    Stamp  TIMESTAMP(3),
    Msg    VARCHAR2(4000)
);

CREATE SEQUENCE seq_LogMsg#ID
    START WITH 0   INCREMENT BY 1
    MINVALUE 0     MAXVALUE 4294967295
    ORDER   NOCACHE   NOCYCLE;

CREATE OR REPLACE
PROCEDURE LogMsg (pMsg  IN VARCHAR2)
IS PRAGMA AUTONOMOUS_TRANSACTION;
    tmp  VARCHAR2(4000);
BEGIN
  tmp := RTrim( SubStr( pMsg, 1, 4000));
  INSERT INTO tbl_LogMsg (ID, Stamp, Msg)
      VALUES (seq_LogMsg#ID.NEXTVAL, SYSTIMESTAMP, tmp);
  COMMIT WORK;
EXCEPTION
  WHEN OTHERS THEN   ROLLBACK WORK;
END LogMsg;
/

------------------------------------------------------------------
-- create a job to execute the procedure (created in the next step)
BEGIN
  DBMS_Scheduler.Create_Job(
      Job_Name           => '"This is the Job to look after"',
      Job_Type           => 'PLSQL_BLOCK',
      Job_Action         => 'ExecutedAsJob();',
      Start_Date         => NULL,
      Repeat_Interval    => NULL,
      Enabled            => False,
      Auto_Drop          => False,
      Comments           => 'Just a test to see if you can find out the job-name'
  );
END;
/

------------------------------------------------------------------
-- create the full procedure
CREATE OR REPLACE
PROCEDURE ExecutedAsJob
IS

    iJobID   INTEGER;
    vcOwner  VARCHAR2(30);
    vcName   VARCHAR2(30);

    FUNCTION GetJobObjectID$ RETURN INTEGER
        IS
            Result     INTEGER;
            iSID       INTEGER;
            iInstance  INTEGER;
        BEGIN
          -- retrieve the actual session-id and the instance-number
          Result := Sys_Context( 'UserEnv', 'BG_Job_ID');

          IF (Result IS NULL) THEN
            iSID      := Sys_Context( 'UserEnv', 'SID');
            iInstance := Sys_Context( 'UserEnv', 'Instance');

            -- retrieve the id of the actual job which is in fact
            -- the object-id of the scheduler job
            -- (assertion: there is only one running job at a time
            --             for the a specific session)
            SELECT srj.Job_ID
                INTO Result
                FROM gv$Scheduler_Running_Jobs srj
                WHERE     (srj.Inst_ID = iInstance)
                      AND (srj.Session_ID = iSID);

            LogMsg( 'GetJobObjectID$() - SID: ' || iSID || ', Instance: '
                    || iInstance);
          END IF; -- (Result IS ...

          RETURN (Result);

        EXCEPTION
          WHEN OTHERS THEN
              LogMsg( SQLERRM);
              LogMSg( DBMS_Utility.Format_Error_BackTrace());
              RETURN (NULL);
        END GetJobObjectID$; -- local to ExecutedAsJob

BEGIN -- of ExcecutedAsJob
  LogMsg( 'Procedure/Job started');
  DBMS_Lock.Sleep( 1.0);

  -- get the job-/object-id
  iJobID := GetJobObjectID$();
  IF (iJobID IS NOT NULL) THEN
    LogMsg( 'Found this running job - ID: ' || iJobID);
    DBMS_Lock.Sleep( 1.5);

  ELSE
    LogMsg( 'Ooops - no running job found and goodbye ...');
    RETURN;
  END IF;

  -- get the owner and name of the job
  SELECT o.Owner, o.Object_Name
      INTO vcOwner, vcName
      FROM DBA_Objects o
      WHERE (o.Object_ID = iJobID);
  LogMsg( 'Job-Object: "' || vcOwner || '"."' || vcName || '"');

  DBMS_Lock.Sleep( 2.5);
  LogMsg( 'Procedure/Job ended');

EXCEPTION
  WHEN OTHERS THEN   LogMsg( SQLERRM);
END ExecutedAsJob;
/

------------------------------------------------------------------
-- code to run the job and query to see the results
BEGIN
  DELETE FROM tbl_LogMsg;
  COMMIT WORK;
  DBMS_Scheduler.Enable( '"This is the Job to look after"');
END;
/

SELECT Sys_Context( 'UserEnv', 'SID') AS "SID", t.*
FROM tbl_LogMsg t ORDER BY t.ID;

-- after 5 seconds the query should give a result like:
/*
SID  ID  STAMP                    MSG
---- --- ------------------------ ----------------------------------------------------
66   0   2014-08-07 11:26:06,930  Procedure/Job started
66   1   2014-08-07 11:26:07,932  Found this running job - ID: 266922
66   2   2014-08-07 11:26:09,434  Job-Object: "TEST2"."This is the Job to look after"
66   3   2014-08-07 11:26:11,935  Procedure/Job ended
*/

------------------------------------------------------------------
-- clean up the database
/*
exec DBMS_Scheduler.Drop_Job( '"This is the Job to look after"');
DROP PROCEDURE ExecutedAsJob;
DROP PROCEDURE LogMsg;
DROP SEQUENCE seq_LogMsg#ID;
DROP TABLE tbl_LogMsg PURGE;
*/

The fellow in need reported that this did solve his problem. Thanks, Niel!

Of course, the Oracle Dev Gym community is full of helpful and expert folks, so I did receive other ideas as well. I offer them below, with the caveat that I have not tested them myself. Thanks to everyone for their assistance!

From Chris Saxon

Hi Steven,

I saw your post about the getting the name of a job from within it on PLCH. This approach does that.

The view USER_SCHEDULER_RUNNING_JOBS show jobs that are active. Querying this, filtering on the current session id will return the current job (if you're within one). The query is:

SELECT job_name FROM user_scheduler_running_jobs

where  session_id = sys_context('USERENV', 'SID');

A full script to show this is below

-- 

Thanks,

Chris

www.sqlfail.com

create table job_name ( name varchar2(100) )

/

create or replace procedure store_job as

begin

  insert into job_name ( name )

    SELECT job_name FROM user_scheduler_running_jobs

    where  session_id = sys_context('USERENV', 'SID');   

  commit;

end;

/

BEGIN

    DBMS_SCHEDULER.CREATE_JOB (

            job_name => '"CHRIS"."TEST_JOB"',

            job_type => 'STORED_PROCEDURE',

            job_action => 'CHRIS.STORE_JOB',

            number_of_arguments => 0,

            start_date => NULL,

            repeat_interval => NULL,

            end_date => NULL,

            enabled => FALSE,

            auto_drop => FALSE,

            comments => '');

    

    DBMS_SCHEDULER.enable(

             name => '"CHRIS"."TEST_JOB"');

             

    SYS.dbms_scheduler.run_job ('TEST_JOB');

END;

/

SELECT * FROM job_name

/

From Cristi Boboc

I do not have much experience either with this package but I think the name of the job can be obtained by the following algorithm:

1. I get the session under which the process execute,

2. From the active running Jobs I get the one which runs in the same session.

A pseudo-code (I do not have an environment to test - therefore kindly please excuse my mistakes) could look like:

SELECT owner, job_name, running_instance, session_id, j.* 

  FROM all_scheduler_running_jobs j
WHERE session_id = sys_context('USERENV','SID')

or, if the "old way of scheduling jobs":

SELECT job, instance, sid, j.* FROM dba_jobs_running j
WHERE session_id = sys_context('USERENV','SID')

From Zoltan Fulop

You raised a question on PL/SQL Challenge regarding DBMS_SCHEDULER. Since I worked a lot with that package let me share my experience about how to get the job name within the code that is being executed by the job. You can use the dictionary view called user_scheduler_running_jobs which lists the currently running jobs or you can simply get the v$session.action attribute by the sys_context('USERENV', 'ACTION') if you're running that job in a background process. Here you can find an example:

CREATE TABLE plch_log (job_name VARCHAR2(100));

CREATE OR REPLACE PROCEDURE plch_proc
IS
  l_job_name user_scheduler_running_jobs.job_name%TYPE;
BEGIN
  BEGIN
    SELECT job_name
      INTO l_job_name
      FROM user_scheduler_running_jobs
     WHERE running_instance = SYS_CONTEXT ('USERENV', 'INSTANCE')
       AND session_id = SYS_CONTEXT ('USERENV', 'SID');
  EXCEPTION
    WHEN no_data_found THEN
      l_job_name := SYS_CONTEXT ('USERENV', 'ACTION');
  END;

  INSERT INTO plch_log VALUES (l_job_name);
  
  COMMIT;
END;
/

DECLARE
  l_job_name VARCHAR2(100) := dbms_scheduler.generate_job_name('PLCH_');
BEGIN
  DBMS_SCHEDULER.create_job(
    job_name            => l_job_name
   ,job_type            => 'STORED_PROCEDURE'
   ,job_action          => 'PLCH_PROC'
   ,enabled             => TRUE
   ,auto_drop           => TRUE);
   
   DBMS_SCHEDULER.run_job(
    job_name            => l_job_name
   ,use_current_session => FALSE);
END;
/

BEGIN
  DBMS_LOCK.sleep(5);
END;
/

SELECT * FROM plch_log;

DROP PROCEDURE plch_proc;

DROP TABLE plch_log;

From Iudith Mentzel

I have zero experience with DBMS_SCHEDULER, but, on a quick glance, maybe the following could help (I did not try it, it is just a "dry" idea)::

SELECT JOB_NAME
  FROM  USER_SCHEDULER_RUNNING_JOBS
 WHERE SESSION_ID       = SYS_CONTEXT('USERENV','SID')
   AND   RUNNING_INSTANCE = SYS_CONTEXT('USERENV','INSTANCE')
/

Another way would be to use the DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT, which can pass the JOB_NAME (and other job metadata ) to the program executed by the job, but, as far as I understand, the program should be prepared/defined to accept that argument, so it is maybe less generic.

*About that reference to Oracle Database Developer

I used to talk about PL/SQL developers and APEX developers and SQL developer and so on, but I have recently come to realize that very, very few Oracle technologists can be “pigeon-holed” that way. Sure, Steven knows and uses only PL/SQL (and SQL), but just about everyone else on the planet relies on a whole smorgasbord of tools to build applications against Oracle Database. So I’m going to start referring to all of us simply as Oracle Database Developers.