Tips on using oracle.jbo.domain.Array as a NamedWhereClauseParam value

Sometime back I've blogged on Using oracle.jbo.domain.Array with ViewCriteria. Let me revisit the same topic with slightly different usage. There are many use cases where oracle.jbo.domain.Array is used as a NamedWhereClauseParam value, ViewObjectImpl::setNamedWhereClauseParam(name,value). If you have such use cases and at run time you hit the following exception, then this blog post is for you ;)

oracle.jbo.JboException: Cannot insert/update Array without context information at oracle.jbo.domain.Array.prepareForDML(Array.java:773) at oracle.jbo.server.ViewRowSetImpl.prepareLobObjectForBind(ViewRowSetImpl.java:8154) at oracle.jbo.server.ViewRowSetImpl.getParametersAsStorageTypes(ViewRowSetImpl.java:5004) at oracle.jbo.server.ViewRowSetImpl.getParametersAsStorageTypes(ViewRowSetImpl.java:4979) at

Solution

If you define bind variable at design time, you may need to key in ColumnType(db object to hold Array that you are passing) as well along with other attributes. Please see my previous post if you are not aware of this part. All works well in this case. When you use ViewObjectImpl::defineNamedWhereClauseParam(...) to define bind variable for Array at run time, the definition is missing this entry and you may hit the above said error. The solution is to set the required context while creating oracle.jbo.domain.Array as shown in the following code snippet.

String[] deptArray = { "Administration","Purchasing" };
Array arr = new Array(deptArray);
HashMap context = new HashMap();
context.put(DomainContext.ELEMENT_SQL_NAME, "CHARTABLETYPE");
context.put(DomainContext.ELEMENT_TYPE, String.class);
arr.setContext(null, null, context);

I'm copying the relevant code below for your reference.

 public void findDepartmentsForArrayParam() {  
  ViewObjectImpl deptVOImpl = getDepartmentsView1();  
  deptVOImpl.defineNamedWhereClauseParam("ArrayOfDeptNames", null,  
                                                null);  
  deptVOImpl.setWhereClause("Departments.DEPARTMENT_NAME   
      IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))");  
  deptVOImpl.setNamedWhereClauseParam("ArrayOfDeptNames",  
                                              getValueAsArray());  
  deptVOImpl.executeQuery();  
 }  
 public Array getValueAsArray() {  
  Array arr = null;  
  try {  
      String[] deptArray = { "Administration","Purchasing" };  
      arr = new Array(deptArray);  
      HashMap context = new HashMap();  
      context.put(DomainContext.ELEMENT_SQL_NAME, "CHARTABLETYPE");  
      //CHARTABLETYPE is DB object
      //CREATE OR REPLACE TYPE  "CHARTABLETYPE"  as table of varchar2(4000);
      context.put(DomainContext.ELEMENT_TYPE, String.class);  
      arr.setContext(null, null, context);  
  } catch (Exception ex) {  
      ex.printStackTrace();  
  }  
  return arr;  
 }