Quite often, a procedure needs to know some information about the session it is running in - at least for logging some information.
It seems to be easy to find this information - just look at SYS_CONTEXT documentation, and find the impressive list of attributes of USERENV context. And if something is missing, just grab the SESSIONID, SID or AUDSID and query v$session or other view.
Well, this works nicely, as long as you don't do it in a job. Unfortunately, in a job you always get 0.
You can try to battle with FG_JOB_ID and BG_JOB_ID, but I think this deserves a more elegant solution - and that's why I always query v$mystat to get the SID of my process. Just fetch the first line:
The next question that comes is: if I am run by a job (let's talk about dbms_job now), what's my job id?
The universal solution is to query v$lock and check for the JQ lock you are holding - and id2 is jor id:
For dbms_scheduler, see for example discussion at http://forums.oracle.com/forums/thread.jspa?messageID=2335238. I still personally don't like it, it's too cumbersone for the things I do (a workflow engine, for example).
It seems to be easy to find this information - just look at SYS_CONTEXT documentation, and find the impressive list of attributes of USERENV context. And if something is missing, just grab the SESSIONID, SID or AUDSID and query v$session or other view.
Well, this works nicely, as long as you don't do it in a job. Unfortunately, in a job you always get 0.
You can try to battle with FG_JOB_ID and BG_JOB_ID, but I think this deserves a more elegant solution - and that's why I always query v$mystat to get the SID of my process. Just fetch the first line:
select * from v$session where sid = (select sid from v$mystat where rownum=1)You need a select grant on v_$mystat by SYS, but as this view does not expose anything important, it's not a security issue.
The next question that comes is: if I am run by a job (let's talk about dbms_job now), what's my job id?
The universal solution is to query v$lock and check for the JQ lock you are holding - and id2 is jor id:
select id2 from v$lock where type = 'JQ' and sid = (select sid from v$mystat where rownum=1)However, there is one more elegant solution - the dbms_job engine does not run just the "what" code specified by you - it wraps it in a short anonymous block. In this block, a variable called "job" is defined and binded to contain just the id you need. So, in your "what" code, just use this variable:
dbms_job.submit(j, 'my_proc(job, my_param1, my_param2);'If you are curious, this block will be run:
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN my_proc(job, my_param1, my_param2);
:mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;
END IF; END;
For dbms_scheduler, see for example discussion at http://forums.oracle.com/forums/thread.jspa?messageID=2335238. I still personally don't like it, it's too cumbersone for the things I do (a workflow engine, for example).
Comments