OAF - Prepared and Callable Statement

CALLABLE STATEMENT

OADBTransaction txn = am.getOADBTransaction();  
                
CallableStatement callableStatement =  
txn.createCallableStatement("begin package_name.procedure_name(:1, :2, :3); end;",OADBTransaction.DEFAULT);  

try {  
callableStatement.setInt(1, param_id);
callableStatement.registerOutParameter(2,OracleTypes.VARCHAR); 
callableStatement.registerOutParameter(3,OracleTypes.ARRAY,"APPS.CUSTTYPEOBJECT");

callableStatement.execute();  

String v_result = callableStatement.getString(2); 
ARRAY arr = ((OracleCallableStatement)callableStatement).getARRAY(3);
String[] recievedArray = (String[])(arr.getArray());
              
for(int i=0;i<recievedArray.length;i++)
System.out.println("element" + i + ":" + recievedArray[i] + "\n");
              
ArrayList  errMsg = new ArrayList(recievedArray.length);

for(int i=0;i<recievedArray.length;i++)
errMsg.add(new OAException(recievedArray[i], OAException.INFORMATION));

if (errMsg.size() > 0)
{
OAException.raiseBundledOAException(errMsg);
}

callableStatement.close();  

} catch (SQLException sqle) {  
  if (pageContext.isLoggingEnabled(2)) {
 pageContext.writeDiagnostics((Object)this, "Exception Occcured", 2);
  } 
}

PREPARED STATEMENT

String Query = 
"SELECT NVL2(FFVT.DESCRIPTION,FFVT.DESCRIPTION||'-'||to_char(sysdate,'yyyy'),FFVT.DESCRIPTION) CUSTOM_VAL\n" + 
"FROM FND_FLEX_VALUE_SETS FFVS ,\n" + 
"  FND_FLEX_VALUES FFV,FND_FLEX_VALUES_TL FFVT\n" + 
"WHERE FFVS.FLEX_VALUE_SET_ID          = FFV.FLEX_VALUE_SET_ID\n" + 
"AND FFV.FLEX_VALUE_ID = FFVT.FLEX_VALUE_ID\n" + 
"AND FFVT.LANGUAGE        = 'US'\n" + 
"AND FFV.ENABLED_FLAG                  = 'Y'\n" + 
"AND NVL(FFV.END_DATE_ACTIVE,SYSDATE) >=SYSDATE\n" + 
"AND FFVS.FLEX_VALUE_SET_NAME          = 'CUST_VALUE_SET'\n" + 
"AND FFV.FLEX_VALUE=:1";

try
{
conn = am.getOADBTransaction().getJdbcConnection();
ps = conn.prepareStatement(Query);
ps.setString(1, ParamVal);
for (rs = ps.executeQuery(); rs.next(); pageContext.writeDiagnostics(this, "Message", 2))
{
invoicePrifixVal = rs.getString("CUSTOM_VAL");
}
rs.close();
ps.close();
} catch (Exception e)
{
throw new OAException(e.getMessage());
}