Fri. Apr 23rd, 2021

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

 99 total views,  2 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *