How to use procedure to kill jobs calling it from java code?

Question

I want to kill some jobs from a java app by calling the procedure below and passing the parameters which are job id, session id and session serial number. It won't work and I am very lost here.

PROCEDURE kill_batch_test ( 
V_JOB IN VARCHAR2, 
V_SID IN VARCHAR2,
V_SERIAL IN VARCHAR2  ) 
IS                
BEGIN           

      DBMS_JOB.REMOVE(V_JOB);

      execute immediate 'Alter System Kill Session '''|| to_char (V_SID)  || ',' || to_char (V_SERIAL) || ''' IMMEDIATE';  


EXCEPTION

   WHEN OTHERS THEN

RETURN;

END;

[EDIT]

The java code : This gets me the user jobs :

    String sql1 = "select sid SID, username, serial# SERIAL,j.job JOB, WHAT from (select  dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE,dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT from dba_jobs dj ) j,(select p.spid, s.username, s.sid, s.serial# from v$process p, v$session s where p.addr  = s.paddr AND s.username = 'EPS') s ";

 SQLQuery res =  (SQLQuery) session.createSQLQuery(sql1).setCacheable(false);
 res.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
 List e = res.list();
 Object o;
 Iterator it = e.iterator();
 for(int i=0;i<e.size();i++){
    o = it.next();
    Map m = (Map)o;
    String sid = null;
    String serial = null;
    String job = null;
    sid = m.get("SID").toString();
    serial = m.get("SERIAL").toString();
    job = m.get("JOB").toString();

here I call the procedure :

    CallableStatement storedProc = hibConnection.prepareCall(sql);
    storedProc.setString(1,job);
    storedProc.setString(2,sid);
    storedProc.setString(3,serial);
    storedProc.executeUpdate();
}

"It won't work" - what do you mean by this? Does it throw an exception? Does it fail to stop the job? Something else? Incidentally, *please* don't write `WHEN OTHERS THEN RETURN` - if there's a problem stopping the job or killing the session, the exception that Oracle throws may tell you what the problem is. — Luke Woodward, Aug 31 '15 at 14:19
@LukeWoodward I am sorry I not good with plsql I have to deal with a client app. It doesn't throw any problem, what else to write tom make return the exception? It runs an says executed but the jobs are still there not remove nor the sessions are killed — TheSM, Aug 31 '15 at 14:23
@TheSM: to stop that procedure swallowing all exceptions that get thrown, delete the lines `EXCEPTION`, `WHEN OTHERS THEN` and `RETURN;` from it. — Luke Woodward, Aug 31 '15 at 14:37
@LukeWoodward great now it gives exceptions, it says : insufficient privileges at line 13 ( where system alter kill is) any suggestions? — TheSM, Aug 31 '15 at 14:45

score 1 · Accepted Answer · answered Aug 31 '15 at 15:04

1

Firstly, as pointed out in the comments, the EXCEPTION WHEN OTHERS THEN RETURN section of your procedure really isn't helping you. To stop it swallowing exceptions, simply delete it.

To fix the 'insufficient privileges' error that then appears, connect to your database as a suitably-privileged user and run GRANT ALTER SYSTEM TO <user your Java app connects as>. Be careful with this privilege as there are a lot of other possible ALTER SYSTEM statements that your user now has permission to run.

Finally (you may already be aware of this), consider moving from DBMS_JOB to DBMS_SCHEDULER. In particular, the latter has a more robust way of stopping jobs without the forceful approach you are using which involves finding the session used to run the job and killing that session.

answered Aug 31 '15 at 15:04

Luke Woodward

63,336
16
89
104

Another good reason for this code for not working is that it does not work with Oracle clusters (RAC). if your application is deployed on a cluster the session ID is not sufficient to identify uniquely the session you want to kill. In a cluster environment you need to add also the @nodenumber optional parameter, otherwise it will it will work only if the sesdion you want to kill is running on the same node your session is running – Carlo Sirna Sep 03 '15 at 20:46
In a rac environment the command should be alter system kill session 'sid, serial, @node' otherwise it will work only if you are connected to the same node that is running the session you want to kill. – Carlo Sirna Sep 03 '15 at 20:52

How to use procedure to kill jobs calling it from java code?

1 Answers1