12. Calling PL/SQL Function from OAF Page

Here is a simple demo for how to call a PL/SQL function from OAF page.

I have created a very basic page having one LOV and a submit button. User will select employee number from LOV field and then click on Go button.

On clicking 'Go' button, we call a PL/SQL function which taking this employee number as parameter and will return employee name and job for that particular employee.

Finally we will display that information as a message to the user.

Below is the function which retrieves the information for an employee number:

CREATE OR REPLACE FUNCTION xx_get_emp_details (p_empnum IN  VARCHAR2)
RETURN VARCHAR2
    AS
       v_name   VARCHAR2 (10);
       v_job    VARCHAR2 (10);

    BEGIN

       SELECT ename, job

         INTO v_name, v_job

         FROM employee

        WHERE empno = p_empnum;

       RETURN (   'Employee info :: '

               || 'Employee Number: '

               || p_empnum

               || '  Employee Name: '

               || v_name

               || '  Employee Job: '

               || v_job

              );

    END;

1) Create a new OA page:
====================
Right click on project (xxcus) –> New –> Web Tier –> OA Components –> select ‘Page’ item. Click OK. (This will open a popup window)
Specify the details of page as below:
Name: XxPlSqlFuncDemoPG
Package: xxcus.oracle.apps.fnd.plsqlfuncdemo.webui

2) Create a new view object for employee number LOV (VO):
====================
Right click (plsqlfuncdemo) –> New View Object (This will open a wizard having 7 steps).

Step 1
Package: xxcus.oracle.apps.fnd.plsqlfuncdemo.lov.server
Name: XxEmpNumLovVO
Choose the radio button ‘Read-only Access’ (as there is no DML operation). Click Next.

Step 2
Enter the below query in ‘Query Statement’:
select empno from employee

As there is no need to generate VOImpl/VORowImpl, keep defaults for step3, 4, 5, 6 & 7 and click Finish. Save All.


3) Create a new Application Module (AM):
====================
Step 1
         Package: xxcus.oracle.apps.fnd.plsqlfuncdemo.server
         Name: XxPlSqlFuncDemoAM. Click Next.

Step 2
         Here we will add an instance of the VO created in (2).
       Select XxEmpNumLovVO from ‘Available View Objects’ and shuttle it to ‘Data Model’ using “>” button.

Keep defaults for all other steps (3, 4). Click Finish.

5) Create a new controller:
====================

Right click on pageLayout region –> set new controller (This will open a popup window). Enter the below details:
           Package Name: xxcus.oracle.apps.fnd.plsqlfuncdemo.webui
           Class Name: XxPlSqlFuncDemoCO

Creating the Page Layout & Setting its Properties:
====================

Page will appear as below:

1) Create a new region under pageLayout of type defaultSingleColumn.
2) Create 2 items under defaultSingleColumn region (messageLovInput, submitButton).

Now change the properties for each field from property inspector as shown below:

pageLayout:
ID: pageLayoutRN

AM Definition: xcus.oracle.apps.fnd.plsqlfuncdemo.server.XxPlSqlFuncDemoAM

Window Title: PL/SQL Function Demo Page
Title: PL/SQL Function Demo

defaultSingleColumn:
ID: singleColRN
Text: Choose Employee Number:

messageLovInput (for Employee Number): 
ID: empNumLov
Prompt: Employee Number

submitButton:
ID: goBtn, Prompt: Go

Creating LOV regions:
Expand empNumLov LOV, right click on region1 and create a new region using wizard.

Step 1
select ‘XxPlSqlFuncDemoAM’ from Application Module drop down and select 'XxEmpNumLovVO1' in available view usages. Click Next.

Step 2
choose region style as ‘table’ from drop down. Click Next.

Step 3
Shuttle all the fields from available to selected attributes. Click Next.
Also modify the prompt to make it more user friendly (like Employee Number).

This will create an item 'Empno' under 'XxEmpNumLovVO1' table region.
Set search allowed property to TRUE for the item created.

lovMap1:
LOV region item: Empno
Return item: empNumLov
Criteria item: empNumLov


The declarative page structure in jDev will be similar to as shown below:

We will catch go button event in PFR method of controller and call the method (callPlSqlFunction) form AM which will retrive information for the employee number selected.

If employee LOV is empty, throw an error message 'Please choose employee numer.'

public void processFormRequest(OAPageContext pageContext, 
                               OAWebBean webBean) {
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule am = null;
    am = pageContext.getRootApplicationModule();
    if (pageContext.getParameter("goBtn") != null) {
        if (!("".equals(pageContext.getParameter("empNumLov").trim()))) {
            String empNum = pageContext.getParameter("empNumLov");
            Serializable[] param = { empNum };
            String empInfo = 
                am.invokeMethod("callPlSqlFunction", param).toString();
            throw new OAException(empInfo, OAException.INFORMATION);
        } else {
            throw new OAException("Please choose employee numer.", 
                                  OAException.ERROR);
        }
    }
}

Below is code for callPlSqlFunction method in AM class:

public String callPlSqlFunction(String empNum) {
    String empInfo = "";
    String stmt = "BEGIN :1 := xx_get_emp_details(:2); end;";
    CallableStatement cs = 
        getOADBTransaction().createCallableStatement(stmt, 1);
    try {
        cs.registerOutParameter(1, Types.VARCHAR);
        cs.setString(2, empNum);
        cs.execute();
        empInfo = cs.getString(1);
        cs.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return empInfo;
}

Finally, information will be displayed as shown in below screenshot: