I have a "star" database. One is doing some work; another two have jobs that pull metadata into their reference tables, from the first database. I want to stop anyone from updating, deleting or inserting any records on the reference tables in the two "slaves"; the tables should just be updated by the scheduled job.
I'm currently doing this with a trigger that checks to see if the current SID is in USER_SCHEDULER_RUNNING_JOBS with the job name I expect to be running. I'd like to change this to use the JOB_ID of the job I'm running from.
This is my current set-up; assume a really simple table:
create table a ( b number );
and the following job:
begin
dbms_scheduler.create_job(
job_name => 'test_job'
, job_type => 'PLSQL_BLOCK'
, job_action => 'begin
merge into a a
using ( select 1 x from dual@db2 ) b
on (1 = 2)
when not matched then
insert values (b.x);
commit;
end;'
, start_date => sysdate
, repeat_interval => 'FREQ = MINUTELY;'
, enabled => true
);
end;
/
I'm using this trigger:
create or replace trigger tr_blah
before insert or update on a
declare
l_ct number;
begin
select count(*) into l_ct
from user_scheduler_running_jobs
where session_id = sys_context('USERENV','SID')
and job_name = 'TEST_JOB'
;
if l_ct = 0 then
raise_application_error(-20000, 'FAIL');
end if;
end;
/
This is inelegant; but, worse, I have to create a separate trigger for each table in each database and change the job name each time. There's no way of dynamically creating the trigger; this gets tiresome and there's a lot of scope for errors creeping in.
SYS_CONTEXT() has the parameters FG_JOB_ID and BG_JOB_ID. Their description, especially that of FG_JOB_ID implies that they might be the JOB_ID of the currently running job. Changing the trigger to the following (I've tried both):
create or replace trigger tr_a
before insert or update or delete on a
declare
l_ct number;
begin
raise_application_error(-20000, sys_context('USER_ENV', 'BG_JOB_ID'));
end;
/
Results in the following
ORA-20000:
ORA-06512: at "REF.TR_A", line 4
ORA-04088: error during execution of trigger 'REF.TR_A'
ORA-06512: at line 2
This implies that both FG_JOB_ID and BG_JOB_ID are null. Is there a method of determining the ID of the job running in the current session so I don't need to use JOB_NAME each time?
