Java code only works when submitted the second time

Question

I have the piece of code displayed below. My challenge is that the code only works the second (third and so on) times it's submitted. I change nothing between the two submits but the first time doesn't do what it's supposed to. Both time I get a job# returned as if everything is fine.

The procedure 'execute_plan' is supposed to update some rows in a table and this is not done until the second submit.

I have tried monitoring the USER_LOGS table and can see no difference whatsoever between the first and second submit.

I have tried replacing the call to another schema with a simple update on a table in the executing users schema. This works the first time.

So the problem seems to be related to calling a procedure in another schema.

EDIT: I have also tried to manually add conn.commit();, I have added commits in the PL/SQL but all in vain :-(

The entire logic is called from a java rest service.

BasicDataSource bds = Util.getDatasource(nodeData);

String plsql = "declare x number; begin x := dlcm_agent.runner.execute_plan(" + nodeData.get("lcPlanId") + "); end;";

Connection conn = null;
JSONObject json = new JSONObject();

try {
    conn = bds.getConnection();
    CallableStatement stmt = conn.prepareCall("begin dbms_job.submit(?,?); end;");

    stmt.setString(2, plsql);
    stmt.registerOutParameter(1, Types.BIGINT);

    stmt.execute();

    json.put("success", true);
} catch (Exception e) {
    json.put("success", false);
    json.put("message", e.getMessage());
} finally {
    if (conn != null) conn.close();
}

return json.toString();

This is driving me insane so if anyone has any input please let me know

Answer 1

First, it would be good to close stmt, that has been used. Also, it's recommended to use executeUpdate for stmts that make some data manipulations.

And third, dbms_job.submit - just submit job to jobs queue. It does not execute it (you probably know it).

Answer 2

Turned out to be an unhandled race condition. I updated a table before the submitted job had completed which caused an error.

Thanks