Requirement: I have a PL/SQL Procedure with the name proc_getempdetails having an input parameter ip_emp_no and two output parameters op_f_name and op_l_name
When the user provides the valid ip_emp_no and invokes the proc_getempdetails procedure it should return the corresponding First Name and Last Name for that Employee.
Solution: For the solution of the above requirement follow the steps as shown below:
Step 1: Create an Oracle PL/SQL Procedure with the name as proc_getempdetails hhaving an input parameter ip_emp_no and two output parameters op_f_name and op_l_name.
Thus, the complete code for the proc_getempdetails procedure is shown below:
CREATE OR REPLACE PROCEDURE proc_getempdetails (
ip_emp_no IN NUMBER,
op_f_name OUT VARCHAR2,
op_l_name OUT VARCHAR2
) IS
BEGIN
SELECT
first_name,
last_name
INTO
op_f_name,
op_l_name
FROM
employees
WHERE
employee_id = ip_emp_no;
END;Step 2: Create and ADF Fusion Web Application.
Create an AppModule in the Model project. Generate an AppModuleImpl.java class.
Write the codes in the AppModuleImpl.java class to pass the ip_emp_no and get the corresponding op_f_name and op_l_name. We will create the two methods: callStoredProcedure and callProcGetEmployeeDetails
Thus, the complete code for the AppModuleImpl.java class is shown below:
package model;
import java.sql.CallableStatement;
import java.sql.Types;
import java.sql.SQLException;
import oracle.jbo.JboException;
import oracle.jbo.server.ApplicationModuleImpl;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Tue Mar 30 19:03:34 IST 2021
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class AppModuleImpl extends ApplicationModuleImpl {
/**
* This is the default constructor (do not remove).
*/
public AppModuleImpl() {
}
protected void callStoredProcedure(String stmt, Object[] bindVars) {
CallableStatement st = null;
try {
st = this.getDBTransaction().createCallableStatement("begin " + stmt + ";end;", 0);
st.setObject(1, bindVars[0]);
st.registerOutParameter(2, Types.VARCHAR);
st.registerOutParameter(3, Types.VARCHAR);
st.executeUpdate();
System.out.println("First Name is : " + st.getString(2) + " and the Last Name is : " + st.getString(3));
} catch (SQLException e) {
throw new JboException(e.getMessage());
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void callProcGetEmployeeDetails() {
callStoredProcedure("proc_getempdetails(?,?,?)", new Object[] { 121 });
}
}Step 3: Let us now Test the AppModule. For this Right Click AppModule > Click Run
Thus, we will get the Oracle ADF Model Tester window as shown below.
Click on AppModule > Method should be callProcGetEmployeeDetails > Click on Execute button
Thus, we can see the Result as Success and the Return Value as NULL, and in the console we can see the result as “First Name is : Adam and the Last Name is : Fripp” as shown below.
This means our call to the Oracle PL/SQL Procedure is successful.
Hence, the solution to our requirement.
If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.
Thanks & Regards,
Susanto Paul
916 total views, 4 views today