Hi,
I’m using below JDBC code to invoke the 2 stored procedures sequentially which updates 2 tables.
After I update 1st Stored proc, I execute a select statement to get the object_version_number of the second table before I execute second store proc…
Here the problem is the Select statement returns the correct object versionNumber, But second store proc is not working and throws error saying that object version number is invalid.
Interestingly when I execute the same procedure from SQL developer using the object version number which I get from select statement, It works.
ANy suggestions in code will help.
String selectQuery = “select OBJECT_VERSION_NUMBER, ASSIGNMENT_ID from per_all_assignments_f WHERE PERSON_ID = ? order by object_version_number desc”;
Statement stmt = connection.prepareStatement(selectQuery);
stmt.setInt( 1, personId);
rs = stmt.executeQuery();
rs.next();
int objectVersionNumber = rs.getInt(1);
int asgId = rs.getInt(2);
//}
log.error("objectVersionNumber from the assignment table: " + objectVersionNumber);
String thirdProcCall = "{call hr_assignment_api.update_emp_asg_criteria(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
CallableStatement thirdStmt = connection.prepareCall(thirdProcCall);
int pId = asgId;
int onum = 1;
String jId = getAttributeRequestValue(account,"JOB_ID");
int jobId = Integer.parseInt(jId);
String pgId = getAttributeRequestValue(account,"PEOPLE_GROUP_ID");
int peopleGroupId = Integer.parseInt(pgId);
thirdStmt.setString(1, getAttributeRequestValue(account,"EFFECTIVE_START_DATE"));
thirdStmt.setString(2, "CORRECTION");
thirdStmt.setInt(3, pId);
thirdStmt.setInt(4, onum);
thirdStmt.setInt(5, jobId);
thirdStmt.setInt(6, peopleGroupId);
thirdStmt.registerOutParameter(7, java.sql.Types.INTEGER);
thirdStmt.registerOutParameter(8, java.sql.Types.VARCHAR);
thirdStmt.registerOutParameter(9, java.sql.Types.DATE);
thirdStmt.registerOutParameter(10, java.sql.Types.DATE);
thirdStmt.registerOutParameter(11, java.sql.Types.BOOLEAN);
thirdStmt.registerOutParameter(12, java.sql.Types.BOOLEAN);
thirdStmt.registerOutParameter(13, java.sql.Types.BOOLEAN);
thirdStmt.registerOutParameter(14, java.sql.Types.VARCHAR);
thirdStmt.registerOutParameter(15, java.sql.Types.BOOLEAN);
thirdStmt.execute();
connection.commit();
result.setStatus(ProvisioningResult.STATUS_COMMITTED);