Oracle callablestatement example in oaf

Oracle callablestatement example in oaf

Callablestatement helps to execute the PL/sql block from OAF page controller. We pass PL/SQL block as string paramters in the Callablestatment and callablestatement help to convert this sting in to an executable plsql block to exeute in the Oracle database.Callablestatement also helps to set the parameter for the block in the OAF page controller. If we are using , parameters in the PLSQL block and we are executing this block from
OAF controller then we also set the values in these parameters with the help of the callablestatement. Callablestatement also helps to get the output of the plsql block.

 

 

Here below I am sharing the examples of callablesstatement in oaf.

 

Example 1:- OAApplicationModule am = (OAApplicationModule)paramOAPageContext.getApplicationModule(paramOAWebBean);

 

OADBTransaction txn = am.getOADBTransaction();

 

String str="BEGIN xx_test_pkg.updaterecord(P_Header_id => :1);END;";

 

OracleCallableStatement Callstmt=(OracleCallableStatement)txn.createCallableStatement(str,1);

String Result = "";
Number localNumber;

try {
Callstmt.setString(1,RptHdrId);
Callstmt.execute();      
Callstmt.close();

 }
catch(SQLException sqle)
  {
paramOAPageContext.writeDiagnostics(this, "Exception in calling sqlstatement  "+sqle, 2);        

Example2:-


OAApplicationModule am = (OAApplicationModule)paramOAPageContext.getApplicationModule(paramOAWebBean);

 

OADBTransaction txn = am.getOADBTransaction();

 

String str="BEGIN xx_test_pkg.updaterecord(P_Header_id => :1);END;";

 

OracleCallableStatement Callstmt=(OracleCallableStatement)txn.createCallableStatement(str,1);

String Result = "";
Number localNumber;

try {
Callstmt.setString(1,RptHdrId);
Callstmt.execute();      
Callstmt.close();

 }
catch(SQLException sqle)
  {
paramOAPageContext.writeDiagnostics(this, "Exception in calling sqlstatement  "+sqle, 2);        
}