ADF Call Stored Procedure with Out Parameters

Step1  Create DB Procedure

stored_db_proc(Param1 IN VARCHAR2,
                           Param2 IN DATE,
                           x_errCode OUT VARCHAR2,
                           x_errMsg OUT VARCHAR2);

Step 2  Create Public/Protected method in ApplicationModule to call

import oracle.jbo.server.DBTransaction; 

public String[] callStoredProcOutParam(String p_param1,String p_param2Date) {

String _xErrCode;

String _xErrMsg;

DBTransaction trx =(DBTransaction)getTransaction(); 

CallableStatement st = null;

try  {

// 1. Define the PL/SQL block for the statement to invoke

String stmt = "begin stored_db_proc (?,?,?,?); end;";

// 2. Create the CallableStatement for the PL/SQL block

st = trx.createCallableStatement(stmt,0);

// 3. Register the positions and types of the OUT parameters

st.registerOutParameter(3,Types.VARCHAR);

st.registerOutParameter(4,Types.VARCHAR);

// 4. Set the bind values of the IN parameters

st.setObject(1,p_param1);

st.setObject(2, p_param2Date);

// 5. Execute the statement

st.executeUpdate();

//6. Set value of _xErrCode  property using first OUT param

_xErrCode =  st.getString(3);

//7. Set value of _xErrMsg property using 2nd OUT param

_xErrMsg =  st.getString(4); 

// 8. Return the result

return new String[] {_xErrCode,_xErrMsg} ;

} catch (SQLException e)  {

                             throw new JboException(e);

} finally  {

if (st != null) {

try {

// 9. Close the JDBC CallableStatement

st.close();

}

catch (SQLException e) {

e.getStackTrace();

}

}

}

}

Step3    Expose callStoredProcOutParam method in PageDefBindings

 Add method in Application Module Client

                Double click AppMOdule àCkick Java à Client Interface

(Note : This will add Interface
public class csvAMClient extends ApplicationModuleImpl implements csvAM)

Refresh Data Control to see newly added  method.

Open Page Def Bindings, Click + Button , Select Method Action,

Click on DataControl Select Method to be included.

Step 4 In Managed Bean Listner method

import oracle.binding.DataControl;
            import oracle.binding.OperationBinding; 

import oracle.adf.model.BindingContext;
            import oracle.adf.model.binding.DCBindingContainer;

........

FacesContext context = FacesContext.getCurrentInstance();

Application app = context.getApplication();

DCBindingContainer binding = (DCBindingContainer) app.evaluateExpressionGet(context, "#{bindings}",DCBindingContainer.class);

// Call AM insContData Method

OperationBinding callAmMethod =

binding.getOperationBinding("callStoredProcOutParam ");

callAmMethod.getParamsMap().put("p_param1",Val1);

callAmMethod.getParamsMap().put("p_param2Date ",castToJBODate(Val2));

callAmMethod.execute();

       

        Object ReturnValue[] = (Object[])callAmMethod.getResult();

       

        String _errCode = (String)ReturnValue[0];

        String _errMsg  = (String)ReturnValue[1];

 

 

 

 

For more Information

http://www.baigzeeshan.com/2010/05/calling-plsql-procedure-and-function-in.html

37.5 Invoking Stored Procedures and Functions