Custom SDO method with View Criteria using IN clause

IN Clause in Custom method:

1. Create type:
CREATE OR REPLACE TYPE  "IDTABTYPE"  as table of varchar2(4000);

2. Create a bind variable that will hold all the input ID's
dept_names_var -> oracle.jbo.Array
ColumnType="IDTABTYPE"
ElemType="java.lang.String"
Like :

  <Variable
    Name="dept_names_var"
    Kind="where"
    Type="oracle.jbo.domain.Array"
    ElemType="java.lang.String"
    ColumnType="IDTABTYPE"/>

3. Create VC using this variable: DepartmentNameVC
DepartmentName = Bind Var -> dept_names_var
Like :
 ( ( ( UPPER(DepartmentsEO.DEPARTMENT_NAME) = UPPER(:dept_names_var)  )  OR  ( :dept_names_var IS NULL ) ) )


4. Generate VORowImpl and VOImpl

5. In VOImpl create methods for creating IN Clause , one for fetch from DataBase and one for cache
 
  Like :
  whereCluase =this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:dept_names_var AS IDTABTYPE)))";  


   
6. In VOImpl

 Override:
 - protected void bindParametersForCollection [Can avoid overriding this]
 - public String getCriteriaItemClause

 Inside getCriteraItemClause fetch the IN clause created in methods created in step 5

6. Generate AMImpl class

 Create custom method that will be exposed in SDO for fetching departments based on list of department names.

   //custom Method
     public List<DepartmentsVORowImpl> findDeptsByNames(List<String> listOfNames) {
   
         List<DepartmentsVORowImpl> dept_list = new ArrayList<DepartmentsVORowImpl> ();
         DepartmentsVOImpl vo = (DepartmentsVOImpl)this.getDepartmentsVO1();
         vo.setApplyViewCriteriaName("DepartmentNameVC");
         vo.setNamedWhereClauseParam("dept_names_var", listOfNames.toArray());
         vo.executeQuery();
         while (vo.hasNext()) {
           DepartmentsVORowImpl r = (DepartmentsVORowImpl)vo.next();
          dept_list.add(r);
         }
           return dept_list;
     }
7. create service interface for AM

 - select the custom method, select the DepartmentVO from LOV

Define Bind variable as optional (uncheck required while creating Bind var), else you will get this error while executing any other operation on same VO
JBO-27122: SQL error during statement preparation.

Thats all !! it works