Skip to main content

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;  
 }  

Comments

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…