Skip to Main Content
  • Questions

  • DBMS_JOB and SYS_CONTEXT ('USERENV', 'SERVICE_NAME')

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tony.

Asked: March 23, 2017 - 11:50 pm UTC

Last updated: October 13, 2017 - 3:41 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi. When scheduling a job using DBMS_JOB, I make use of the value in SYS_CONTEXT ('USERENV', 'SERVICE_NAME'). As an example, I have this procedure:

CREATE OR REPLACE PROCEDURE proc_test
IS
BEGIN
   INSERT INTO test_table (test_value)
        VALUES (SYS_CONTEXT ('USERENV', 'SERVICE_NAME'));

   COMMIT;
END proc_test;


When I run the procedure manually, I get the correct DB name inserted into the table. The problem is when I schedule the procedure to run via DBMS_JOB it inserts the value SYS$USERS into the table instead.

Are you able to explain why?

Regards

Tony


and Connor said...

To assign jobs to a service, you create a job class, eg

begin
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
     job_class_name=>'USE_A_PARTICULAR_SERVICE',
     service=>'MY_SERVICE_NAME');
end;


and then schedule jobs using that class

    dbms_scheduler.create_job (
       job_name           =>  'MY_JOB',
       job_type           =>  'PLSQL_BLOCK',
       job_action         =>  'my_block;',
       ...
       ...
       job_class          =>'USE_A_PARTICULAR_SERVICE');



Rating

  (2 ratings)

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

Comments

Tony Cardone, March 24, 2017 - 3:24 am UTC

Fantastic. Will try this out. Thanks!
Connor McDonald
March 25, 2017 - 3:39 am UTC

glad we could help

Does this answer the sys_context query for a job?

Andrew Hardy, October 12, 2017 - 9:39 am UTC

Like the OP, I'd like to determine 'where' my procedure is being run.

Like the OP, I'm using sys_context('sys_context('userenv','service_name') to get the service name of the database connection. I then use this information to determine which email servers, etc. the environment should be accessing, whether certain processes are to be run, etc.

When called from by a user process the sys_context brings back the expected values e.g. pdb_gin_dev.myserver.com, pdb_gin_test.myserver.com, gin_production.customer.com, etc.

When called from a job (in this case dbms_jobs), the value returned is SYS$USERS.

So, when running as a job how do I determine the service name?
Connor McDonald
October 13, 2017 - 3:41 am UTC

That is *indeed* the service name that the job is running under.

You can force a job to run under a particular service using the job class, eg

begin
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
     job_class_name=>'MY_JOB_CLASS',
     service=>'MY_SERVICE');
end;
/

grant execute on sys.MY_JOB_CLASS to [user];


and then submit jobs with that job class.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library