PL/SQL Challenge Community to the rescue! 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

Soon after, I received a solution from Niels Hecker, one of the top PL/SQL Challenge quiz players, and all-around very helpful guy. He wrote:

I have found a really easy solution: in the context "UserEnv" there is an element "BG_Job_ID" which is not a real id of the job but the id of the job-object in the database (DBA_Objects.Object_ID). Here’s the code I wrote leveraging this fact:

-- 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 PL/SQL Challenge 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

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.

Comments

  1. I must confess that the idea of using the value of the setting 'BG_Job_ID' in the context 'UserEnv' was inspired by the article at:

    https://groups.google.com/forum/#!topic/comp.databases.oracle.server/zpiUesiD6XM

    ReplyDelete
  2. Hi Steven,

    There is a much simpler way of achieving this. Just use the variable named JOB_NAME which will be declared automatically by Scheduler for PLSQL_BLOCK jobs.

    Example:

    create table job_output( tstamp timestamp with time zone, name varchar2(128) );

    begin
    dbms_scheduler.create_program(
    program_name => 'P1',
    program_type => 'PLSQL_BLOCK',
    program_action => 'insert into job_output values (systimestamp, job_name); commit;',
    enabled => true
    );
    end;
    /

    begin
    dbms_scheduler.create_job(
    job_name => 'JOB_1',
    program_name => 'P1',
    enabled => true
    );
    end;
    /

    SQL> select * from job_output;

    TSTAMP
    ---------------
    NAME
    ---------------
    16-DEC-15 09.03.07.409398 AM -08:00
    JOB_1

    Thanks!
    Jose-Luis

    ReplyDelete
  3. Hi Steven

    Thanks for the article. Using the user_scheduler_running_jobs in combination with sys_context('USERENV', 'SID') was the way to go.

    Just a quick comment for people who are not only scheduling their jobs, but also do a manual start by using the dbms_scheduler.run_job procedure.
    Make sure you pass as the second argument FALSE, because the default is TRUE and then the user_scheduler_running_jobs table is not updated for some reason in oracle 12C.

    dbms_scheduler.run_job ('JOB_NAME', false);

    Regards

    Yannick

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts