VO based on RefCursor returned from a stored procedure

ADF view objects can be heavily customized and works well most of the times. There may be sitautions when we need to create VO based on ref cusor returned by a stored procedure/function. Althogh there is no shortcut for this but definitely an extension to existing VO.

Steps:
- Create Proc/Function that returns ref cursor
   Function get_empdata(p_emp_id number,p_cursor OUT ref_cursor);
- Create a VO , select rows populated programmatically
- Dont create any attrs
- Once done with wizard and VOImpl is generated
Create the attrs in VO that you want (these are transient var)
    Like:
    Emp_id Number
    FirstName,LastName as string

- create a bind variable , this is used to pass the param for proc/function call dept_id Number

override the following methods in VOImpl
  -create
  -executeQueryForCollection
  -hasNextForCollection
  -createRowFromResultSet
  -getQueryHitCount
  -releaseUserDataForCollection
 
  write your custom method for calling the stored proc/function, like i created something like :
protected Object callStoredFunction(int sqlReturnType, String stmt,
        Object[] bindVars) {
        CallableStatement st = null;
        try {
          st = getDBTransaction().createCallableStatement("begin ? := " + stmt +
              "; end;", 0);
          st.registerOutParameter(1, sqlReturnType);
          if (bindVars != null) {
            for (int z = 0; z < bindVars.length; z++) {
              st.setObject(z + 2, bindVars[z]);
            }
          }
          st.executeUpdate();
          return st.getObject(1);
        }
        catch (SQLException e) {
          throw new JboException(e);
        }
      }

This VO is just like any other Read only VO and can have links to other view objects as well.

This works !!