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

In your catch block do e.printStackTrace() and post it here, please — Sully, May 22 '14 at 09:20

score 0 · Answer 1 · answered May 22 '14 at 09:24

0

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).

answered May 22 '14 at 09:24

vvg

6,325
19
36

Thanks @mst but both suggestions came up with nothing :-( – CJe May 22 '14 at 09:36
What are values of success and message after first execution? – vvg May 22 '14 at 09:55
success is true and message is undefined – CJe May 22 '14 at 09:58
can you modify your sql from prepareCall. Just to make sure that script is executing. For ex.: `begin insert into log_table (col1) values "someValue"; dbms_job.submit(?,?); end;` I believe nothing is happening in dlcm_agent or it's executed without any output that you can see. – vvg May 22 '14 at 10:00
I have already tried this with a simple 'update table ...'. That works right away. This is why I think it's related to executing a proc in another schema. – CJe May 22 '14 at 10:47

score 0 · Answer 2 · answered May 23 '14 at 08:39

0

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

Thanks

answered May 23 '14 at 08:39

CJe

1,928
3
24
53

Java code only works when submitted the second time

2 Answers2