OAF: Sample code to call PL/SQL Function from OA Framework

Please find below sample code to call PL/SQL function from OA Framework page.

 import oracle.apps.fnd.framework.server.OADBTransaction;  
 import oracle.jdbc.driver.OracleTypes;  
 import oracle.jdbc.OracleCallableStatement;  
 public String callFunction( String id)  
 {  
      String status = null;
      OADBTransaction oadbtransaction = getOADBTransaction();  
      OracleCallableStatement oraclecallablestatement = null;  
      Object obj = null;  
      String sqlString = "BEGIN :1:=xx_pkg.xx_func( p_id => :2 ); END;";  
      try  
      {  
           oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(sqlString , 1);  
           oraclecallablestatement.registerOutParameter(1, OracleTypes.VARCHAR, 0, 10);  
           oraclecallablestatement.setString(2, id);  
           oraclecallablestatement.executeQuery();  
           status = String.valueOf(oraclecallablestatement.getString(1));  
      }  
      catch(Exception exception1)  
      {  
           throw OAException.wrapperException(exception1);  
      }  
      finally  
      {  
           try  
           {  
                oraclecallablestatement.close();  
           }  
           catch(Exception exception2)  
           {  
                throw OAException.wrapperException(exception2);  
           }  
      }  
      return status ;  
 }  

Feel free to point out if anything is missing/wrong in this blog.

OAF: Sample code to call PL/SQL Procedure from OA Framework

Please find below sample code to call PL/SQL procedure from OA Framework page.

In the below example first 4 parameters are IN parameters and the last 3 parameters are OUT parameters.This example code is a function defined in the ApplicationModule.

 import oracle.jdbc.OracleCallableStatement;   
 import oracle.jdbc.OracleTypes;
 import oracle.jbo.domain.Number;
 import oracle.jbo.domain.Date;

    
 public void callPLSQLProc(Number id,String code,Date currentDate)   
 {   
      OracleCallableStatement callableStatement = null;   
      try   
      {   
           String callProc = " BEGIN xxaj_pkg.xxaj_proc "+   
                                         "(p_id             => :1," +   
                                         " p_code           => :2," +
                                         " p_date           => :3," +
                                         " p_commit         => :4," +                                                  
                                         " x_return_status  => :5," +   
                                         " x_msg_data       => :6," +   
                                         " x_msg_count      => :7);"+   
                                    " END; ";   
           callableStatement = (OracleCallableStatement)getOADBTransaction().createCallableStatement(callProc,1);   
           callableStatement.setNUMBER(1, id);   
           callableStatement.setString(2, code);           
           callableStatement.setDATE(3, currentDate);     
           callableStatement.setString(4, "Y");      
           callableStatement.registerOutParameter(5,OracleTypes.VARCHAR,255);   
           callableStatement.registerOutParameter(6,OracleTypes.VARCHAR,255);   
           callableStatement.registerOutParameter(7,OracleTypes.NUMBER,255);     
           callableStatement.execute();   
           String resultMessage = (String)callableStatement.getString(5);   
           String msgData  = (String)callableStatement.getString(6);   
      }   
      catch(Exception e)   
      {   
           e.printStackTrace();   
           throw new OAException(e.toString(),OAException.ERROR);   
      }   
      finally   
      {   
           try   
           {   
                callableStatement.close();   
           }   
           catch(Exception exception2)   
           {   
                throw OAException.wrapperException(exception2);   
           }   
      }   
 }         

Package Spec is as below:

 create or replace package xxaj_pkg as  
 procedure xxaj_proc (p_id            in number  
                     ,p_code          in varchar2  
                     ,p_date          in date  
                     ,p_commit        in varchar2  
                     ,x_return_status out varchar2  
                     ,x_msg_data      out varchar2  
                     ,x_msg_count     out number);  
 end xxaj_pkg;  

Feel free to point out if anything is missing/wrong in this blog.