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

Comments

  1. 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.

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

    ReplyDelete
  3. Thanks for sharing. Very usefull tip!

    ReplyDelete
  4. 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

    ReplyDelete
  5. 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

    ReplyDelete
  6. 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

    ReplyDelete
  7. 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 ?

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

    ReplyDelete
  9. 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.

    ReplyDelete
  10. The XML has been removed.

    So,

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

    ReplyDelete
  11. 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.

    ReplyDelete
  12. 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"

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

    ReplyDelete
  14. 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

    ReplyDelete
  15. 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.

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

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

    ReplyDelete

Post a Comment