Fri. Apr 23rd, 2021

Requirement: I have a PL/SQL Stored Function with the name as fn_getDepartmentName having an input parameter i_dept_id

When the user provides the valid i_dept_id and invokes the fn_getDepartmentName function it should return the corresponding Department Name.

Solution: For the solution of the above requirement follow the steps as shown below:

Step 1: Create an Oracle PL/SQL Stored Function with the name as fn_getDepartmentName having an input parameter i_dept_id.

Thus, the complete code for the fn_getDepartmentName function is shown below:

CREATE OR REPLACE FUNCTION fn_getDepartmentName (
    i_dept_id NUMBER
) RETURN VARCHAR2 IS
    lv_dept_name VARCHAR2(100) := 'No Department Found';
BEGIN
    SELECT
 department_name
    INTO lv_dept_name
    FROM
        departments
    WHERE
        department_id = i_dept_id;

    RETURN lv_dept_name;
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 deptId and get the corresponding deptName. We will create the two methods: getDepartmentName and callStoredFunction

Thus, the complete code for the AppModuleImpl.java class is shown below:

package model;

import model.common.AppModule;

import oracle.jbo.server.ApplicationModuleImpl;

import java.sql.CallableStatement;


import java.sql.SQLException;

import java.sql.Types;

import oracle.jbo.JboException;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Mon Mar 29 18:01:56 IST 2021
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class AppModuleImpl extends ApplicationModuleImpl implements AppModule {
    /**
     * This is the default constructor (do not remove).
     */
    public AppModuleImpl() {
    }

    public String getDepartmentName(Integer deptId) {
        String deptNm = "No Department Found";
        Object deptName = callStoredFunction(Types.VARCHAR, "fn_getDepartmentName(?)", new Object[] { deptId });
        if (deptName != null) {
            deptNm = deptName.toString();
        }
        return deptNm;
    }

    private Object callStoredFunction(int sqlReturnType, String statement, Object[] bindVariable) {
        CallableStatement callableStatement = null;
        try {
            //Creating sql statement
            callableStatement = this.getDBTransaction().createCallableStatement("begin ? := " + statement + ";end;", 0);
            //Register dataType for return value
            callableStatement.registerOutParameter(1, sqlReturnType);
            //Pass input parameters value
            if (bindVariable != null) {
                for (int i = 0; i < bindVariable.length; i++) {
                    callableStatement.setObject(i + 2, bindVariable[i]);
                }
            }
            callableStatement.executeUpdate();
            //Finally get returned value
            return callableStatement.getObject(1);
        } catch (SQLException e) {
            throw new JboException(e.getMessage());
        } finally {
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Expose the getDepartmentName method as a Client Interface.

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 getDepartmentName > Provide deptId value as 10 > Click on Execute button

Thus, we can see the Result as Success and the Return Value as Administration.

This means our call to the Oracle PL/SQL Store Function is successful.

Step 4: Now, we will invoke the PL/SQL Stored Function from the jspx page.

For this, let me create the demo.jspx page.

Drag and drop af:panelBox and set text=”Call PL/SQL Stored Function in Oracle ADF” and showDisclosure=”false”

Drag and drop getDepartmentName method from the AppModuleDataControl on the demo.jspx page as an ADF Parameter Form

This will create both the deptId inputText and the button to get the departmentName.

Change the label for the deptId af:inputText as Department ID

Change the text for the af:button as Get Department Name

Also, drag and drop the _return_type_ as an af:inputText just after the af:button. Set the label as Department Name and partialTriggers=”b1″

Thus, the complete code for the demo.jspx page is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1" xmlns:f="http://java.sun.com/jsf/core"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <jsp:directive.page contentType="text/html;charset=UTF-8"/>
    <f:view>
        <af:document title="demo.jspx" id="d1">
            <af:messages id="m1"/>
            <af:form id="f1">
                <af:panelBox text="Call PL/SQL Stored Function in Oracle ADF" id="pb1" showDisclosure="false">
                    <f:facet name="toolbar"/>
                    <af:panelFormLayout id="pfl1">
                        <af:inputText value="#{bindings.deptId.inputValue}" label="Department ID"
                                      required="#{bindings.deptId.hints.mandatory}"
                                      columns="#{bindings.deptId.hints.displayWidth}"
                                      maximumLength="#{bindings.deptId.hints.precision}"
                                      shortDesc="#{bindings.deptId.hints.tooltip}" id="it1">
                            <f:validator binding="#{bindings.deptId.validator}"/>
                            <af:convertNumber groupingUsed="false" pattern="#{bindings.deptId.format}"/>
                        </af:inputText>
                        <af:button actionListener="#{bindings.getDepartmentName.execute}" text="Get Department Name"
                                   disabled="#{!bindings.getDepartmentName.enabled}" id="b1"/>
                        <af:inputText value="#{bindings.return.inputValue}" label="Department Name"
                                      required="#{bindings.return.hints.mandatory}"
                                      columns="#{bindings.return.hints.displayWidth}"
                                      maximumLength="#{bindings.return.hints.precision}"
                                      shortDesc="#{bindings.return.hints.tooltip}" id="it2" partialTriggers="b1">
                            <f:validator binding="#{bindings.return.validator}"/>
                        </af:inputText>
                    </af:panelFormLayout>
                </af:panelBox>
            </af:form>
        </af:document>
    </f:view>
</jsp:root>

Save all and run the application. Thus, the ran application is shown below:

Provide the Department ID value as 20 and click on the Get Department Name button

Thus, we can see the Department Name as Marketing.

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

 152 total views,  5 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 *