Tuesday, April 8, 2014

Dynamic where condition in DB Adapter - Query By Example

Dynamic where condition in DB Adapter - Query By Example

Sometime we may have the scenario to query the data in a table with dynamic where condition through DB Adapter in Oracle SOA Suite 11g.
The Query by Example option in the DB Adapter provides the option to construct the where condition dynamically based on the input data provided.Query-by-example uses the AND operator to tie the input attributes together to form the where condition.



For example when we are defining the Query By Example option for the Employee table the adapter will consider the Employee object as input and returns the Employee Object list.


If we are invoking the Adapter without passing the value for any of the attribute the adapter will return all the Employee records that is the query will be executed without where condition(SELECT EMPNO, EMPNAME, EMPAGE, DEPNAME, CONTACTNO FROM EMPLOYEE).


If we are setting value for the attributes , the attributes will be added into the where condition of the query connected by the AND operation(SELECT EMPNO, EMPNAME, EMPAGE, DEPNAME, CONTACTNO FROM EMPLOYEE WHERE ((EMPNO = ?) AND (EMPNAME = ?))) and the adapter will returns the records satisfied by the condition.




2 comments:

  1. Hi Albin,

    Lets say if input parameters has date range. How do we handle this?We cannot do Select * from table where Date1 and Date;

    Thanks.

    ReplyDelete
    Replies
    1. You can try something like this

      SELECT *
      FROM order_details
      WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
      AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

      Delete