Tuesday, 18 December 2012

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)
       v_name   VARCHAR2 (10);
       v_job    VARCHAR2 (10);

       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

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:

ID: pageLayoutRN
AM Definition: xcus.oracle.apps.fnd.plsqlfuncdemo.server.XxPlSqlFuncDemoAM
Window Title: PL/SQL Function Demo Page
Title: PL/SQL Function Demo

ID: singleColRN
Text: Choose Employee Number:

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

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.

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.", 

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);
        empInfo = cs.getString(1);
    } catch (Exception e) {
    return empInfo;

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


  1. I tried the same thing, but I am getting "no method with signature error", please help me to get out of this error.

    Thanks in Advance.


    1. At which point you are getting this error ?
      Put some debug messages using SOP and check the same.

      Also check if that PL/SQL function is created in your apps schema or not.


  2. am getting invlid index column error on below line. what might be the issue

    2, empNum);