Tips on using oracle.jbo.domain.Array as 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

mukesh said…
This comment has been removed by the author.
mukesh said…
Thanks Jobinesh,
Should we create CHARTABLETYPE in the DB if we donot have a bind variable at all or not created declaratively?
This is information i needed thanks to sharing the wonderful articles
Hi..Jobinesh Very interesting article you have got here. I love reading this kind of stuff. This is truly a great read for me.
Jobz said…
Hi Jobinesh,
Need a small help. How can I implement this in version 10.1.3.2

While adding the same code, I am getting error at setContext section
Jobz said…
Hi jobinesh...
The code is deployed without any errro. But it is not showing the result after execution... the row count is displayed as 0..

I will contact u via email soon. i need ur help
Anonymous said…
Hi Jobinesh,
Thanks for your blog. This has been of great help to me. I followed exactly what is said in the blog and it worked well perfectly.
In Oracle documentation for array's setContext() says "Applications should not use this method". Is it safe to use this method in applications.
I am new to java, can you please help me understand what this method does?

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of my employer.