Call PL/SQL Procedure from OAF Page

Step 1:- Create a package in APPS

CREATE OR REPLACE PACKAGE XXC05_test_package

IS

PROCEDURE data_sum

(   item1            IN      NUMBER,

    item2            IN      NUMBER,

    data_sum         OUT     NUMBER

);

END XXC05_test_package;

CREATE OR REPLACE PACKAGE BODY APPS.XXC05_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 XXC05_test_package; 

Step 2:- Create a workspace and Project.

                Workspace

                                Name:- Call_ProcWorkspace.jws

                Project

                                Name:- Call_procProject

                                Package:- XXC05.oracle.apps.wip.Call_Proc

Step 3:- Create a Application Module (AM)

                Name:- Call_ProcAM

                Package:- XXC05.oracle.apps.wip.Call_Proc.server

Step 4:- Create a New Page

                Name:- Call_ProcPG

                Package:- XXC05.oracle.apps.wip.Call_Proc.webui

Step 5:- Select region1 and set the following properties:

                ID:-  PageLayoutRN

                Region Style:- pageLayout

                Window Title:- Call Procedure Window

                Title:- Call procedure Title

                AM Defination:- XXC05.oracle.apps.fnd.Call_Proc.server.Call_ProcAM

Step 6:- Add new Region in PageLayoutRN

                ID:- MainRN

                Region Style:- messageComponentLayout

Step 7:- Add 2 New Items

                Item 1:-

                                ID:- item1

                                Region Style:- messageTextInput

                                Prompt:-Text  Item 1

                Item 2:-

                                ID:- item2

                                Region Style:- messageTextInput

                                Prompt:-Text  Item 2

Step 8:- Create other Region into MainRN(messageLayout)

                ID:- ButtonLayout

Step 9:- Create new item

                ID:- Sum

                Region Style:- SubmitButton

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

                Prompt:- Sum

Step 10:-  Add that Code in Call_ProcAMImpl

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( " XXC05_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;

    }

Step 11:- Add that code in Call_ProcCO

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);

       }

  }           

Now Page is Created Successfully