Skip to main content

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

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

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel