Wednesday, 1 April 2015

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.

No comments:

Post a Comment