Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Java code to invoke PL/SQL

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();```

Comments

SiatGesi Feb 14 2025

THE FIRST problem howto wrap I have resolved…

SiatGesi Feb 14 2025

now I'm trying to move the unzipped files (pictures) from db to AS… my real problem… best regards

1 - 2

Post Details

Added on Feb 13 2025
0 comments
22 views