Skip to main content

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?

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…

Happy New Year 2018 !

We can't go back and change the beginning, but we always can start where we are and change the ending. Believe in yourself and you will be unstoppable!

Wishing you and your family a very happy new year 2018 !!!