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="" version="2.1" xmlns:f=""
xmlns:af="">
<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
1,204 total views, 1 views today