DBMS_JOB API Example

DBMS_JOB API example to kill DB session of a concurrent request:
The DBMS_JOB package schedules and manages jobs in the job queue.

Main Procedures that will be used:
DBMS_JOB.SUBMIT submits the job to the job queue. You must issue a COMMIT statement immediately after the statement.
Parameters:
job: Number of the job being run.
what: PL/SQL procedure to run.
next_date: Next date when the job will be run.
interval: Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.
no_parse: A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job. If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.
For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.
instance: When a job is submitted, specifies which instance can run the job.
force: If this is TRUE, then any positive integer is acceptable as the job instance. If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.

DBMS_JOB.RUN runs the job specified.
Parameters:
job: Number of the job being run.
force: If this is TRUE, then instance affinity is irrelevant for running jobs in the foreground process. If this is FALSE, then the job can be run in the foreground only in the specified instance.

Here’s an example on how to use this package to kill the session of a concurrent request from a PL/SQL procedure.

DECLARE

l_mon_req_id NUMBER := fnd_global.conc_request_id;

l_jobnum NUMBER;

BEGIN

–Get session details of the concurrent request you want kill

SELECT d.sid,

d.inst_id,

d.serial#

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager =

b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND d.paddr = c.addr

AND a.request_id = l_mon_req_id;

–Submit the job to kill the session to the job queue

DBMS_JOB.submit (

job => l_jobnum,

what => ‘begin execute immediate ”alter system kill session ””’

|| s.sid

|| ‘,’

|| s.serial#

|| ”””’; end;’,

instance => s.inst_id);

COMMIT;

–Run the job

DBMS_JOB.run (l_jobnum);

END;