Hi,
I'm trying to invoke a PL/SQL procedure from JAVA. I use the below stored proc in SQL developer, It works fine.
WHen I try to execute same procedure using JAVA, I get this error.. Can anyone help me to identify the issue. I'm struggling to find the issue here.
Stored Procedure:
select OBJECT_VERSION_NUMBER into ln_number from per_all_assignments_f WHERE PERSON_ID = ln_person_id;
DBMS_OUTPUT.PUT_LINE('Success in employee update after: ' || ln_number);
hr_assignment_api.update_emp_asg_criteria (
--------------------------------------------------------------
-- In Paramenters (HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA)
--------------------------------------------------------------
p_effective_date => TRUNC (SYSDATE),
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => ln_assignment_id,
p_object_version_number => ln_number,
p_job_id => 688,
p_people_group_id => ln_people_group_id,
--p_supervisor_id => '76523',
--------------------------------------------------------------
-- Out Paramenters (HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA)
--------------------------------------------------------------
p_special_ceiling_step_id => ln_special_ceiling_step_id,
p_group_name => lv_group_name,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_org_now_no_manager_warning => lb_org_now_no_manager_warning,
p_other_manager_warning => lb_other_manager_warning,
p_spp_delete_warning => lb_spp_delete_warning,
p_entries_changed_warning => lv_entries_changed_warning,
p_tax_district_changed_warning => lb_tax_district_changed_warning
);
-- Check if assignment update was successful
IF l_error_message IS NOT NULL THEN
-- Handle any errors from the assignment update
DBMS_OUTPUT.PUT_LINE('Error in assignment update: ' || l_error_message);
ROLLBACK;
RETURN;
END IF;
select OBJECT_VERSION_NUMBER into ln_number1 from per_all_assignments_f WHERE PERSON_ID = ln_person_id;
DBMS_OUTPUT.PUT_LINE('Success in employee update after assignment: ' || ln_number1);
-- Commit if both operations are successful
COMMIT;
……………………………………………………………………………….
JAVA CODE:
```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);
int jobId = 688;
int peopleGroupId = 61;
log.error("objectVersionNumber from the assignment table: " + objectVersionNumber);
String thirdProcCall = "{call hr_assignment_api.update_emp_asg_criteria(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
CallableStatement thirdStmt = connection.prepareCall(thirdProcCall);
thirdStmt.setString(1, getAttributeRequestValue(account,"EFFECTIVE_START_DATE"));
thirdStmt.setString(2, "CORRECTION");
thirdStmt.setInt(3, asgId);
thirdStmt.setInt(4, objectVersionNumber);
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();```