Execute parameterized PL SQL procedure from OAF page

Let us try to call PL/SQL package from OAF page. We will try to send two interger values to one PL/SQL procedure which will do sum of that numbers and will return back sum of that numbers

1. Create a New OA Workspace and Empty OA Project

File> New > General> Workspace Configured for Oracle Applications

File Name -- ParameterizedProcProj

Project Name – ParameterizedProcDemo

Default Package -- parameter.oracle.apps.fnd.parameterizedprocdemo

2. Create Application Module AM

Right click on ParameterizedProcProj > New > ADF Business Components > Application Module

Name -- ParameterizedProcDemoAM

Package -- parameter.oracle.apps.fnd.parameterizedprocdemo.server

Check Application Module Class: ParameterizedProcDemoAMImpl Generate JavaFile(s)

3. Create a New Page

Right click on ParameterizedProcProj > New > Web Tier > OA Components > Page

Name -- ParameterizedProcDemoPG

Package -- parameter.oracle.apps.fnd.parameterizedprocdemo.webui

4. Select region1 and set the following properties:

ID -- PageLayoutRN

Region Style -- PageLayout

AM Definition --parameter.oracle.apps.fnd.parameterizedprocdemo.server.ParameterizedProcDemoAM

Window Title – Execute Paramterized Procedure Demo Page Window

Title – Execute Paramterized Procedure Demo Page Header

Auto Footer – True

5. Add a New Region MainRN

Select PageLayoutRN right click > New > Region

ID -- MainRN

Region Style – messageComponentLayout

6. Create messageTextInput Items

Create item1

Select MainRN > New > messageTextInput

Set following Properties for Text Item1

ID – item1

Item Style – messageTextInput

Data Type -- Number

Prompt – Text Item1

Maximum Length – 20

Length -- 20

Create item2

Select MainRN > New > messageTextInput

Set following Properties for Text Item1

ID – item2

Item Style – messageTextInput

Data Type -- Number

Prompt – Text Item2

Maximum Length – 20

Length -- 20

7. Create a Submit Button

Right Click on MainRN > New > messageLayout

Select newly created messageLayout right click > New > item

Set Following Properties for newly created item

ID – Sum

Item Style – submitButton

Attribute Set -- /oracle/apps/fnd/attributesets/Buttons/Go

Prompt – Sum

8. Run Your Page UI is ready --

9. Let us create a package and package body which we will call from OAF page

This Package takes three parameters all are number. First two are IN parameters and last is OUT as sum of first two numbers

Package Spec

CREATE OR REPLACE PACKAGE APPS.test_package AUTHID CURRENT_USER
IS
PROCEDURE data_sum
(   item1            IN      NUMBER,
    item2            IN      NUMBER,
    data_sum     OUT  NUMBER
);
END test_package;
/
SHOW ERRORS;
EXIT;

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.test_package 
IS

PROCEDURE data_sum
(   item1          IN    NUMBER,
    item2          IN    NUMBER,
    data_sum  OUT NUMBER
)
IS
BEGIN
 data_sum := item1 + item2;
END data_sum;

END test_package;
/
SHOW ERRORS;
EXIT;

10. Add Following Code in your AMImpl Class (ParameterizedProcDemoAMImpl.java)

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.jdbc.OracleCallableStatement;
import java.sql.Types;
import oracle.apps.fnd.framework.OAException; 
...

public String dataSumAction(String item1,String item2)
{ OADBTransaction oadbtransaction = (OADBTransaction)getTransaction();   OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getTransaction();

 String retValues;

 StringBuffer str = new StringBuffer();
 str.append( " BEGIN ");
 str.append( " test_package.data_sum( ");
 str.append( "       item1           => :1, ");
 str.append( "       item2           => :2, ");  str.append( "       data_sum    => :3  ");
 str.append( "    ); ");
 str.append( " END; ");

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

 try{
  oraclecallablestatement.setInt(1,  Integer.parseInt(item1) );
  oraclecallablestatement.setInt(2,  Integer.parseInt(item2) );

  oraclecallablestatement.registerOutParameter(3, Types.VARCHAR);

  oraclecallablestatement.execute(); 
                  
  retValues = oraclecallablestatement.getString(3);
 }  catch(Exception e)
 {
  throw OAException.wrapperException(e);
 }
 return retValues;
}

11. Add Controller for Page ParameterizedProcDemoPG Select PageLayoutRN right click Set New Controller

Package Name -- prajkumar.oracle.apps.fnd.parameterizedprocdemo.webui

Class Name -- ParameterizedProcDemoCO

Add Following Code in Controller

import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import java.io.Serializable; 
...

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);
  
 OAApplicationModule am = pageContext.getApplicationModule(webBean);
    
 if (pageContext.getParameter("Sum") != null)        
 {          
  Serializable[] parameters1 = { pageContext.getParameter("item1"),
       pageContext.getParameter("item2"),
      };
                   
  String retVals1 = (String)am.invokeMethod("dataSumAction", parameters1);

  String message = "Sum:  " + retVals1;                   
  throw new OAException(message, OAException.INFORMATION);
 }
}

12. Congratulation you have successfully finished. Run Your ParameterizedProcDemoPG page and Test Your Work

a

b

c

END