Sunday, 21 July 2013

Execute parameterized PL SQL procedure from OAF page


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


========================================================================

 public void testStoredProcedure() { 
  
     OADBTransaction txn = getDBTransaction();  
     CallableStatement callableStatement =txn.createCallableStatement("begin mmm_procedure(:1, :2); end;",OADBTransaction.DEFAULT);  
     try 
      {  
       callableStatement.registerOutParameter(2, Types.VARCHAR);  
       callableStatement.setString(1, "mahi");  
       String outParamValue = null;  
       callableStatement.execute();  
       outParamValue = callableStatement.getString(1);  
       callableStatement.close();  
     } 
     catch (SQLException sqle) 
     {  
       callableStatement.close(); 
     } 

   } 

No comments:

Post a Comment