26 February, 2012

Calling Stored Procedures or Functions from OAF Page

To invoke a stored procedure or function  from within an entity object or an application module, you need to follow below steps:
1. Create a CallableStatement with the PL/SQL block containing the stored procedure or function invocation
2. Bind any parameters ( IN or OUT )
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters or return value of function
5. Close the statement.

I will illustrate previous steps in below examples

1-Stored Procedure with OUT parameter

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

2- Stored Function

 public void testFunction() {  
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin :1 := xx_function(:2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(1, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
     }  
   }  
Mahmoud Ahmed El-Sayed

2 comments:

  1. Your means of explaining the whole thing in this
    article is genuinely fastidious, all be capable of simply be
    aware of it, Thanks a lot.

    Look into my page ... buy followers

    ReplyDelete
  2. Good Deacription Mr. Mahmoud.

    ReplyDelete

How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...