Thursday, December 2, 2010

Using oracle.jbo.domain.Array with ViewCriteria

Sometimes you may need pass multiple values to the SQL WHERE clause. Apparently you may end up in using SQL IN clause to pass values. A possible solution (using ADF BC) for such use case has been shared in one of my previous post(Using bind variable for....). This is not the only one possibility, you can realize similar use cases using oracle.jbo.domain.Array as well. This approach doesn't require the custom database function that we used in the previous post to parse the comma separated input string. In this post I'm sharing sample application built using oracle.jbo.domain.Array to pass multiple arguments to a SQL statements with IN clause.

A glance at the implementation

In this example user can search departments using comma separated values as shown in the following diagram.


Custom Converter for oracle.jbo.domain.Array

As you can see in the above picture, DepartmentName field in the search panel takes comma separated values. This inputText has a custom converter(view.CustomDomainArrayConverter) added to convert comma delimited values to oracle.jbo.domain.Array. To get a feel of it, please explore the class view.CustomDomainArrayConverter in the sample workspace - attached at the end of this post.

<af:inputText value="#{bindings.ArrayOfDeptNames.inputValue}"
        ......
        id="it1" >
<f:converter converterId="ArrayConverter"/>
</af:inputText>

ViewCriteria Definition

Search part is implemented using a ViewCriteria where the attribute DepartmentName is mapped to a bind variable operand(ArrayOfDeptNames) of type oracle.jbo.domain.Array. Please take a look at the bind variable 'ArrayOfDeptNames' definition in the following diagram.


<Variable
    Name="ArrayOfDeptNames"
    Kind="viewcriteria" ColumnType="CHARTABLETYPE"
    Type="oracle.jbo.domain.Array"
    ElemType="java.lang.String">   
</Variable>

The ColumnType="CHARTABLETYPE" in the above definition is mapped to a custom database type to hold the value types used in the query. Obviously, to run this sample you may need to have this type defined in your database.

CREATE OR REPLACE TYPE  "CHARTABLETYPE"  as table of varchar2(4000);

Generating Native SQL for CriteriaItem having Array with IN clause

I overrode the getCriteriaItemClause(ViewCriteriaItem vci) to generate the native SQL for Array with IN clause. Please see the following code snippet.

@Override
public String getCriteriaItemClause(ViewCriteriaItem vci) {
  if (vci.getAttributeDef().getName().equals("DepartmentName") &&
    vci.getViewCriteria().getName().contains("DepartmentsViewCriteria")) {
    if (vci.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
    return getINClauseForDatabaseUse(vci);
    } else {
    return getINClauseForCache(vci);
    }
  } else {
    return super.getCriteriaItemClause(vci);
  }

}

protected String getINClauseForDatabaseUse(ViewCriteriaItem vci) {

  String whereCluase = "1=1";
  if (getArrayOfDeptNames() != null) {
    whereCluase =
        this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))";
  }
  return whereCluase;
}


protected String getINClauseForCache(ViewCriteriaItem vci) {
  String whereCluase = "1=1";
  return whereCluase;
}


Search form used in this example is built by dragging and dropping the ExecuteWithParams from the Data Control Pallet. DepartmentsViewCriteria, that I explained initially, is mapped to DepartmentsView1 instance in the AppModule.

The final SQL generated at run time may look like as shown below.

SELECT Departments.DEPARTMENT_ID,   Departments.DEPARTMENT_NAME, .... Departments WHERE
( ( Departments.DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))) )

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS2 + HR Schema]

How to run this sample?

1. Unzip the source to your local drive.
2. Setup the required DB objects in your local schema(HR) by running the \select_in_list.sql
3. Run the main.jspx. This page displays query field (which takes comma separated values) and a result table.



Learn More ...

There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html

19 comments:

JCFlack said...

I'm glad to see that using an Array as a bind variable now works in ADF 11g - it didn't work properly in 10g.

If your database is 10g or later, you can replace
DEPARTMENT_NAME IN (SELECT * FROM TABLE(CAST(:ArrayOfDeptNames AS CHARTABLETYPE)))
With
DEPARTMENT_NAME MEMBER OF CAST(:ArrayOfDeptNames AS CHARTABLETYPE)
A lot easier to read IMHO, and I'll bet it runs a bit faster too.

Jobinesh said...

Great JCFlack !...Thank you for pointing this out.

Samuel said...

Thanks for sharing. Very usefull tip!

Morgan said...

Hi Jobinesh,

I have a view object by sql. I want to filter the results by logged in user. The getINClauseForDatabaseUse() in viewImpl uses getEntityDef(0).getAliasName()+...

My view object is not built upon entity and a null pointer exception is thrown as a result.

Please suggest.

Thanks,
Shiva

Jobinesh said...

Siva
Sorry, I'm not getting the clear picture. Please post this in OTN forum detailing the use case with complete error stack trace, and pass me the link. OTN forum - http://forums.oracle.com/forums/forum.jspa?forumID=83&start=0

Nanditha Iyer said...

Hi ,

I implemented the code following the above procedure . But when I tried to debug , the overridden method getCriteriaItemClause(vci) never gets invoked .

Could someone help me with this ?

Thanks
Nanditha

Antoine said...

Hello,

After following the example. I made mine but I got the following exception :

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CHAR

The CHARTABLETYPE is recognized. I do not understand what Oracle is expecting. Any Idea ?

Jobinesh said...

Antoine,
Does it work if you keep
ColumnType="CHARTABLETYPE" for the bind varible in VO XML file?

Antoine said...

Hello Jobinesh,

Thank you for your help on my bug.

My bind variable in my VO :



If I remove the ColumnType attribute I got the same error.

Antoine said...

The XML has been removed.

So,

Name="CodesDestinataires"
Kind="where"
Type="oracle.jbo.domain.Array"
ElemType="java.lang.String"
ColumnType="CHARTABLETYPE"

Jobinesh said...

Antoine,
I can take a look if you can send me the sample where you face the issue. Would be gr8 if I can run it locally.

Antoine said...

I just solved my problem.
But by creating a simple viewCriteriaItem with a bind variable, it does not work.

The generated query was like that :
SELECT * FROM (SELECT
d.code_dest,
l.id_livraison
FROM
LIVRAISON l, DESTINATAIRES d
WHERE
l.id_destinataire = d.id_destinataire) QRSLT WHERE ( ( code_dest MEMBER OF (CAST (:CodesDestinataires AS CHARTABLETYPE)) ) ) ORDER BY "CODE_LIVR"


In the VO, I changed the SELECT and directly added the bind variable.
And now I got this and it works :
SELECT * FROM (SELECT
d.code_dest,
l.id_livraison
FROM
LIVRAISON l, DESTINATAIRES d
WHERE
l.id_destinataire = d.id_destinataire AND d.code_dest MEMBER OF CAST (:CodesDestinataires AS CHARTABLETYPE)) QRSLT WHERE (( 1=1 )) ORDER BY "CODE_LIVR"

Hasim said...

Nice Post , But instead of text box ,is this same scenario possible with af:query | multi select list. thanks,

Jobinesh said...

See the approach given in the following blog help(though it doenot directly use mulit select, the same approach should work for VCItem with multi select values as well) -http://jobinesh.blogspot.in/2010/09/using-bind-variable-for-sql-statements.html

Anonymous said...

thanks for share..

Anonymous said...

Hi Jobinesh,

I have followed approach in my in my code and I am hitting the below error
ORA-00932: inconsistent datatypes: expected - got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"

I tried running the query in sql Developer and i am hitting the same error here as well.
My query looks something like

select * from suppliers where supplier_name in ((select * FROM TABLE(CAST(:1 AS CHARTABLETYPE)));

For bind variable :1 I am passing values as "Supp1,Supp2".
This is throwing the below mentioned error.
Please correct if I am doing anything wrong.

Jobinesh Purushothaman said...

Do you have CHARTABLETYPE created in DB? Dis you try the sample attached in this post?

Anonymous said...

Yes, I have CHARTABLETYPE on my db. I am running the statement on sql developer to check how cast function works in this case.

Stuart Moore said...

Hi. Very useful. But is it possible to not 'hardcode' the database type when defining the variable? So instead of ColumnType="CHARTABLETYPE", we would have something like ColumnType=groovyExpression().
Thanks.