Using PL/SQL Packages, Procedures, and Functions in an OAF Page

Introduction

Oracle Application Framework (OAF) pages frequently need to execute PL/SQL packages, procedures, or functions to perform business logic, retrieve computed values, or manipulate data. Since OAF is built on Java (BC4J), you cannot call PL/SQL directly from the page—you must invoke it through the Application Module using a Callable Statement.

This guide walks you through the complete process of calling a PL/SQL package from an OAF page. We will build a page that:

Displays a Supplier LOV (List of Values)
When a supplier is selected, automatically calculates and displays the Total Standard Invoice Amount for that supplier
Uses a PL/SQL package to perform the calculation

Note: The same technique applies to calling standalone procedures and functions—only the PL/SQL call syntax changes slightly.

The Four Key Activities

To execute PL/SQL from an OAF page, you need to complete four key activities:

#	Activity	Purpose
1	Create the PL/SQL Package	Contains the business logic (procedure/function)
2	Create the Application Module Method	Wraps the PL/SQL call using `OracleCallableStatement`
3	Create the Controller	Invokes the AM method and binds the result to the page
4	Create the Page	Provides the UI (LOV, input fields, buttons)

Prerequisites

Before you begin, ensure the following:

Oracle JDeveloper is installed and configured with a valid Runtime Connection
Database Connection is established
You have an EBS user account with the necessary responsibilities
Access to the APPS schema to create the PL/SQL package
The AP_INVOICES_ALL and PO_VENDORS tables are accessible (for this example)

Step 1: Create the PL/SQL Package

First, create the PL/SQL package in the database. This package contains the procedures that will be called from OAF.

CREATE OR REPLACE PACKAGE APPS.xx_package_test IS
    PROCEDURE XX_STANDARD(VENDOR_CODE IN VARCHAR2, RESULT OUT VARCHAR2);
    PROCEDURE XX_PREPAY(VENDOR_CODE IN VARCHAR2, RESULT_2 OUT VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY APPS.xx_package_test IS

    PROCEDURE XX_STANDARD(VENDOR_CODE VARCHAR2, RESULT OUT VARCHAR2) IS
        A NUMBER;
    BEGIN
        SELECT SUM(INVOICE_AMOUNT)
        INTO A
        FROM AP_INVOICES_ALL A1, PO_VENDORS PV
        WHERE INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
        AND A1.VENDOR_ID = PV.VENDOR_ID
        AND PV.SEGMENT1 = VENDOR_CODE;

        RESULT := A;
    EXCEPTION
        WHEN OTHERS THEN
            A := 0;
            RESULT := A;
    END XX_STANDARD;

    PROCEDURE XX_PREPAY(VENDOR_CODE VARCHAR2, RESULT_2 OUT VARCHAR2) IS
        A NUMBER;
    BEGIN
        SELECT SUM(INVOICE_AMOUNT)
        INTO A
        FROM AP_INVOICES_ALL A1, PO_VENDORS PV
        WHERE INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
        AND A1.VENDOR_ID = PV.VENDOR_ID
        AND PV.SEGMENT1 = VENDOR_CODE;

        RESULT_2 := A;
    EXCEPTION
        WHEN OTHERS THEN
            A := 0;
            RESULT_2 := A;
    END XX_PREPAY;

END xx_package_test;
/

Note: The package has two procedures:
XX_STANDARD — Calculates the total of standard invoices for a supplier
XX_PREPAY — Calculates the total of prepayment invoices for a supplier

Both procedures take a VENDOR_CODE (supplier number) as input and return the calculated total as an OUT parameter.

Step 2: Create the Workspace and Project

Create a New OA Workspace:
- Right-click on Applications
- Select New OA Workspace
- Workspace Name: RohitPackageCalling
- Click OK
Configure the Project:
- Project Name: RohitPackageCalling
- Default Package: rohit.oracle.apps.fnd.RohitPackageCalling
Enter Runtime Connection Details:
- Provide your EBS Username and Password
Complete the Wizard:
- Click Next, then click Finish

Step 3: Create the Application Module

Open the New Gallery:
- Right-click on the project RohitPackageCalling
- Select New
Select Application Module:
- Navigate to Business Tier → ADF Business Components
- Select Application Module
- Click OK
Configure the Application Module:
- Application Module Name: RohitPackageCallingAM
- Default Package: rohit.oracle.apps.fnd.RohitPackageCalling.server
Complete the Wizard:
- Click Next, then click Finish

Step 4: Create the OAF Page

Open the New Gallery:
- Right-click on the project RohitPackageCalling
- Select New
Select Page:
- Navigate to Web Tier → JSF/Faces
- Select Page
- Click OK
Configure the Page:
- Page Name: RohitPackageCallingPG
- Default Package: rohit.oracle.apps.fnd.RohitPackageCalling.webui
Rename the Default Region:
- Select region1
- Change the ID to PageLayoutRN
Attach the Application Module:
- With PageLayoutRN selected, set the AM Definition to:
  text
```
rohit.oracle.apps.fnd.RohitPackageCalling.server.RohitPackageCallingAM
```

Step 5: Create the Main Region and Table Layout

Create the Main Region:
- Right-click on PageLayoutRN
- Select New → Region
- Change the ID to MainRN
- Set Region Style to tableLayout
Create a Row Layout:
- Right-click on MainRN
- Select New → rowLayout
- A region (region1) is created
Create the First Cell:
- Click on region1
- Select New → cellFormat
- A region (region2) is created
Create the Supplier LOV Item:
- Click on region2
- Select New → Item
- Configure the item:

Property	Value
ID	`item1`
Prompt	`Supplier`
Action Type	`firePartialAction`
Event	`update`

Note: The firePartialAction action type and update event enable the item to trigger a partial page refresh when the supplier is selected.

Create the Second Cell (Total Standard):
- Click on region1
- Select New → cellFormat
- A region (region3) is created
- Under region3, create an item:

Property	Value
ID	`item2`
Prompt	`Total Standard`

Create the Third Cell (Total Prepayment):
- Click on region1
- Select New → cellFormat
- A region (region4) is created
- Under region4, create an item:

Property	Value
ID	`item3`
Prompt	`Total Prepayment`

Step 6: Create the Supplier View Object

Open the New Gallery:
- Right-click on the project RohitPackageCalling
- Select New
Select View Object:
- Navigate to Business Tier → ADF Business Components
- Select View Object
- Click OK
Configure the View Object:
- View Object Name: SupplierVO
- Default Package: rohit.oracle.apps.RohitPackageCalling.server

Define the Query:

At Step 5, enter the SQL query:

SELECT vendor_id, segment1, vendor_name
FROM po_vendors
WHERE NVL(disabled_flag, 'N') = 'N'

Click Next, then click Finish

Attach the VO to the Application Module:
- Right-click on RohitPackageCallingAM
- Select Edit RohitPackageCallingAM
- Shuttle SupplierVO to the right
- Click Apply, then click OK
Bind the LOV to Item1:
- Select item1
- Set the External LOV to the SupplierVO-based LOV region
- Configure the LOV mapping to return the supplier number

Note: The LOV setup is covered in detail in the "Creating an LOV in OAF" guide. For this example, ensure the LOV returns the supplier number (SEGMENT1) to item1.

Step 7: Create the Controller

Create the Controller:
- Right-click on PageLayoutRN
- Select Set New Controller
- Controller Name: RohitPackageCallingCO
- Package: rohit.oracle.apps.fnd.RohitPackageCalling.webui

Step 8: Write the Application Module Method

Open the Application Module implementation class (RohitPackageCallingAMImpl.java) and add the following method:

public String XXSTANDARD(String item1)
{
    OADBTransaction oadbtransaction = (OADBTransaction)getTransaction();
    OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getTransaction();
    String retValues;
    StringBuffer str = new StringBuffer();

    str.append(" BEGIN ");
    str.append(" xx_package_test.XX_STANDARD( ");
    str.append("       VENDOR_CODE => :1, ");
    str.append("       RESULT      => :2  ");
    str.append("    ); ");
    str.append(" END; ");

    OracleCallableStatement oraclecallablestatement =
        (OracleCallableStatement)oadbtransaction.createCallableStatement(str.toString(), 1);

    try {
        oraclecallablestatement.setString(1, item1);
        oraclecallablestatement.registerOutParameter(2, Types.VARCHAR);
        oraclecallablestatement.execute();
        retValues = oraclecallablestatement.getString(2);
    }
    catch (Exception e) {
        throw OAException.wrapperException(e);
    }
    return retValues;
}

Code Explanation

Line	Purpose
`OADBTransaction oadbtransaction = ...`	Gets the database transaction object
`StringBuffer str = new StringBuffer();`	Builds the PL/SQL call string
`str.append(" BEGIN ");`	Starts the PL/SQL block
`str.append(" xx_package_test.XX_STANDARD( ");`	Calls the package procedure
`str.append(" VENDOR_CODE => :1, ");`	Binds the input parameter
`str.append(" RESULT => :2 ");`	Binds the output parameter
`str.append(" ); ");`	Closes the procedure call
`str.append(" END; ");`	Ends the PL/SQL block
`OracleCallableStatement oraclecallablestatement = ...`	Creates the callable statement
`oraclecallablestatement.setString(1, item1);`	Sets the input parameter (supplier number)
`oraclecallablestatement.registerOutParameter(2, Types.VARCHAR);`	Registers the output parameter
`oraclecallablestatement.execute();`	Executes the PL/SQL procedure
`retValues = oraclecallablestatement.getString(2);`	Retrieves the output value
`throw OAException.wrapperException(e);`	Wraps any exception in an OAException

Important: The createCallableStatement method takes the PL/SQL block as a string and a parameter indicating the number of parameters. The registerOutParameter method must be called before execute() to receive the output value.

Step 9: Write the Controller Code

Open the controller file (RohitPackageCallingCO.java) and add the following code:

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
    super.processFormRequest(pageContext, webBean);
    
    OAApplicationModule am = pageContext.getApplicationModule(webBean);
    
    if ("update".equals(pageContext.getParameter(EVENT_PARAM)))
    {
        // Get the supplier number from item1
        Serializable[] parameters1 = { pageContext.getParameter("item1") };
        
        // Invoke the AM method to call the PL/SQL procedure
        String retVals1 = (String)am.invokeMethod("XXSTANDARD", parameters1);
        
        // Bind the result to item2
        OAMessageTextInputBean textBean = 
            (OAMessageTextInputBean)webBean.findIndexedChildRecursive("item2");
        textBean.setValue(pageContext, retVals1);
        
        // Display the result as an informational message
        throw new OAException(retVals1, OAException.INFORMATION);
    }
}

Code Explanation

Line	Purpose
`if ("update".equals(...))`	Detects the `update` event fired by the LOV selection
`Serializable[] parameters1 = { pageContext.getParameter("item1") };`	Gets the supplier number from item1
`String retVals1 = (String)am.invokeMethod("XXSTANDARD", parameters1);`	Invokes the AM method and gets the result
`OAMessageTextInputBean textBean = ...`	Finds item2 in the page structure
`textBean.setValue(pageContext, retVals1);`	Sets the result value on item2
`throw new OAException(retVals1, OAException.INFORMATION);`	Displays the result as a message

Step 10: Rebuild and Run the Project

Rebuild the Project:
- Right-click on the project RohitPackageCalling
- Select Rebuild
Run the Page:
- After successful compilation, right-click on RohitPackageCallingPG
- Select Run
Test the Functionality:
- The page opens with a Supplier LOV field
- Select a supplier from the LOV
- The Total Standard field is automatically populated with the calculated amount
- The result is also displayed as an informational message

How PL/SQL Integration Works at Runtime

The following illustrates the complete flow:

┌─────────────────────────────────────────────────────────────────┐
│                        OAF PAGE                                 │
│  1. User selects a supplier from the LOV                        │
│  2. "update" event fires (firePartialAction)                    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                     CONTROLLER                                  │
│  1. Detects "update" event in processFormRequest                │
│  2. Gets supplier number from item1                             │
│  3. Invokes "XXSTANDARD" method on the AM                       │
│  4. Binds the result to item2                                   │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                 APPLICATION MODULE                              │
│  1. Builds the PL/SQL call string                               │
│  2. Creates an OracleCallableStatement                          │
│  3. Sets input parameter (supplier number)                      │
│  4. Registers output parameter                                  │
│  5. Executes the statement                                      │
│  6. Retrieves the output value                                  │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    PL/SQL PACKAGE                               │
│  1. xx_package_test.XX_STANDARD executes                        │
│  2. Queries AP_INVOICES_ALL and PO_VENDORS                      │
│  3. Calculates SUM(INVOICE_AMOUNT)                              │
│  4. Returns the result via the OUT parameter                    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                        DATABASE                                 │
│  1. Query executes against the database                         │
│  2. Result returned to the PL/SQL procedure                     │
│  3. Value passed back to the Application Module                 │
│  4. Displayed on the OAF page                                   │
└─────────────────────────────────────────────────────────────────┘

Understanding the Three Key Mechanisms

Mechanism	Purpose	Key Component
OracleCallableStatement	Executes PL/SQL from Java	`createCallableStatement()`
registerOutParameter	Registers the output parameter	`registerOutParameter(index, Types.VARCHAR)`
invokeMethod	Invokes the AM method from the controller	`am.invokeMethod("methodName", params)`

Why This Approach Works

OAF is built on Java (BC4J), so you cannot call PL/SQL directly from the page. The Application Module acts as the bridge:

The Controller invokes a method on the AM
The AM method builds a PL/SQL call string and uses OracleCallableStatement to execute it
The PL/SQL package performs the business logic and returns the result
The result is passed back through the AM to the controller
The controller binds the result to a page item

Best Practices

Practice	Description
Use Bind Variables	Always use bind variables (`:1`, `:2`) to prevent SQL injection
Handle Exceptions	Wrap PL/SQL calls in try-catch blocks
Register OUT Parameters	Always call `registerOutParameter` before `execute()`
Use OAException	Wrap exceptions in `OAException.wrapperException()`
Close Statements	Close the callable statement after use
Test in Development	Always test PL/SQL calls in a development environment
Use Meaningful Names	Name AM methods and PL/SQL procedures descriptively
Document Parameters	Keep a record of input and output parameters

Troubleshooting Common Issues

Issue	Possible Cause	Solution
PL/SQL procedure not found	Wrong package or procedure name	Verify the package name and procedure signature
No output returned	OUT parameter not registered	Verify `registerOutParameter` is called
Wrong value returned	Wrong parameter order	Verify the bind variable positions
Compilation errors	Missing imports	Add imports for OracleCallableStatement, Types, etc.
Page doesn't update	Event not firing	Verify `firePartialAction` and the event name
SQLException	Invalid SQL or table access	Verify the SQL query and table permissions
NullPointerException	Result not bound to item	Verify the item ID in `findIndexedChildRecursive`

Required Import Statements

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean;
import oracle.jbo.server.OracleCallableStatement;
import java.io.Serializable;
import java.sql.Types;

Summary

Using PL/SQL packages, procedures, and functions in an OAF page involves the following key steps:

Create the PL/SQL Package: Define the procedures/functions with IN and OUT parameters
Create the Application Module Method: Wrap the PL/SQL call using OracleCallableStatement
Create the Controller: Invoke the AM method and bind the result to a page item
Create the Page: Provide the UI (LOV, input fields, buttons)
Rebuild and Test: Compile and verify the functionality

Key Points to Remember

OAF cannot call PL/SQL directly — you must go through the Application Module
OracleCallableStatement is the bridge between Java and PL/SQL
registerOutParameter must be called before execute() to receive output values
invokeMethod is used by the controller to call AM methods
OAException.wrapperException() wraps exceptions in a standard OAF exception
Bind variables (:1, :2) prevent SQL injection and improve performance
firePartialAction enables the page to refresh dynamically when the LOV selection changes

Why This Matters

Integrating PL/SQL with OAF is essential for:

Reusing Existing Business Logic: Leverage existing PL/SQL packages and procedures
Performing Complex Calculations: Execute database-intensive operations efficiently
Enforcing Business Rules: Apply validations and business logic at the database level
Optimizing Performance: Process data close to the database rather than in the middle tier

By mastering PL/SQL integration, you can build OAF pages that leverage the full power of the Oracle database, combining the rich user interface of OAF with the robust business logic of PL/SQL.

References

Oracle E-Business Suite Documentation: OA Framework Developer's Guide
Oracle Database Documentation: PL/SQL Language Reference
Oracle JDeveloper Documentation: Available in the jdevdoc folder
Related Guides: Creating an LOV in OAF, Creating a Search Page in OAF, Controller Extension in OAF, Partial Page Rendering in OAF

Oracle Apps/DBA

Pages

39. Package/Procedure Using in OAF Page

Using PL/SQL Packages, Procedures, and Functions in an OAF Page

Introduction

The Four Key Activities

Prerequisites

Step 1: Create the PL/SQL Package

Step 2: Create the Workspace and Project

Step 3: Create the Application Module

Step 4: Create the OAF Page

Step 5: Create the Main Region and Table Layout

Step 6: Create the Supplier View Object

Step 7: Create the Controller

Step 8: Write the Application Module Method

Code Explanation

Step 9: Write the Controller Code

Code Explanation

Step 10: Rebuild and Run the Project

How PL/SQL Integration Works at Runtime

Understanding the Three Key Mechanisms

Why This Approach Works

Best Practices

Troubleshooting Common Issues

Required Import Statements

Summary

Key Points to Remember

Why This Matters

References

No comments:

Post a Comment

Total Pageviews

Pages

Search This Blog