Shay Shmeltzer's Oracle Development Tools Tips

  • December 4, 2009

ADF Query with Parameters and List of Values - Part II

Shay Shmeltzer
Director of Product Management - Oracle

In the previous blog entry I showed how to do a search page using the execute with parameters operation. This entry is a little more up-to-date and uses the new view criteria and query components that have been added in JDeveloper 11g.

One nice aspect here is that it also lets you change the UI component for the parameters to be input with list of values.

Here is the demo:

Join the discussion

Comments ( 18 )
  • Mike Friday, January 8, 2010
    Great Jdeveloper Demos!!
    i use execute with parameters form and populate a form and/or table.
    i would like to have another button (show details) that passes same params to a query and displays a table with details
    on same page?
    do you know of any online examples?
  • shay Friday, January 8, 2010
    Glad you liked the demo. For the scenario you are describing it seem that all you need is a service method at the AM level that you expose to the client. Then you can drag this method and drop it as an ADF parameter form.
    The method can update a VO definition (set the Where for example) and then you can just add that VO to your page.
  • Suresh Friday, May 21, 2010
    This is nice demo, I liked it. The problem scenario I am working on is bit different.
    I have a VO query like below:
    select job_id, sum(salary) as TotalSal from employees where dept=:p_dept group by job_id
    I want to show the combo box for department id in UI. User will select the department id and he will be able to see the pie chart for the above query.
    Can you please help me with this?
  • shay.shmeltzer Friday, May 21, 2010
    Suresh, seems like an ideal situation for using a navigation list, see this past entry: http://blogs.oracle.com/shay/2008/11/the_navigation_list_select_som.html basically define the department VO as a master for a VO that has your sum query. Drop the first VO as a navigation list - and the detail VO as a graph and you are done.
  • Angel Friday, April 1, 2011
    Hello Shay
    I am having a different use case and I don't know exactly how can I implement it.
    I have two VO's with different queries:
    No1 select * from table where param1, param2
    No2 select count(*) from table where param1, param2
    (The selects are generic)
    I can implement it into a single select:
    select *, (select count(*) from table where param1, param2) where param1 param 2 but it will be very costly in resources, and this is why I decided to create 2 VOs, but my problem is that I must make them react to the same set of params.
    Can you help me with a solution?
    Thanks, Angel
  • shay.shmeltzer Friday, April 1, 2011
    Angel, See if this blog entry helps:
  • guest Wednesday, June 29, 2011

    Hi Shay

    Nice demo! What I am looking for is a way to display the department name on the query component after it has been selected by the list of values. Just a nice confirmation to the user that they have selected the correct one they wanted. In my example there are too many items in the lookup to use the choice list. Any idea if this can be done?

    thanks and regards


  • Shay Friday, July 1, 2011

    Lisa - how about using an LOV component in your af:query instead?

    Similar to this: http://blogs.oracle.com/shay/entry/adding_an_lov_to_a_query_param

  • guest Monday, July 4, 2011

    Hi Shay

    Thanks for the reply.

    I am already using an LOV component in the query component. After the user has made the selection from the LOV, I would like the descriptive name of the selection, rather than the meaningless code, to display on the query component. The analogy in your demo, would be that after choosing Seattle from the city LOV, then on the query component we would see the meaningful Seattle rather than the meaningless 1700.



  • Shay Tuesday, July 5, 2011

    Lisa, you can add the translation field to your VO that gets the deparment name. Basically create the VO so it based on the EMP as an updateable EO and the Dept EO as a refence EO - and include the department_name field in the VO.

  • Lisa Wednesday, July 6, 2011

    Shay, this is almost what I want, thanks. I would just like to have the department id on the employee query component as well as the department name, and would like to have both the department id and name populated from the lov. I thought it might work to add the other item under Edit List of Values, Configuration tab, List Return Values (where it says you can "map any supplemental values that your list returns to the base view object") but this doesn't seem to work in a query component.

  • Rob Thursday, July 14, 2011

    I am looking to accomplish the very same thing that Lisa is asking. I would like to populate both department id and department name from one LOV. I would be very interested to know if this is possible inside the query component.

  • Shay Thursday, July 14, 2011

    Rob and Lisa, it seems that this is not possible right now in the built-in query component. I filed a Bug/ER for this - in the meantime if you have to have this functionality you'll need to build your own query page not using the af:query component.

  • BIJESH k Tuesday, October 11, 2011


    I want to attach method parameters with LOV other than choice one list. And i want to cascade with each other. Could you please guide me



  • Shay Wednesday, October 12, 2011
  • JBaste Monday, May 14, 2012

    Hi Shay,

    I'm facing a little different scenario of your sample.

    MODEL scenario.

    I have a main ViewObject with Search Criteria and LOVs for 3 fields, and all of them receives the same parameter, the logged user role.

    Checking the logged user role, I must to filter what rows will be displayed. So, the query for the main VO is like this:

    SELECT * FROM myTable WHERE userRole = :loggedUserRole

    The queries for my 3 LOVs are similar, the only change is the field.

    LOV 1

    SELECT DISTINCT field_1 FROM myTable WHERE someFilterField IN (

    SELECT otherField FROM myTable WHERE userRole = :loggedUserRole


    LOV 2

    SELECT DISTINCT field_2 FROM myTable WHERE someFilterField IN (

    SELECT otherField FROM myTable WHERE userRole = :loggedUserRole


    LOV 3

    SELECT DISTINCT field_3 FROM myTable WHERE someFilterField IN (

    SELECT otherField FROM myTable WHERE userRole = :loggedUserRole


    field_1 <> field_2 <> field_3 <> userRole. All different fields for the same table.

    VIEW CONTROLLER scenario.

    I have a home page in adfc-config unbounded task flow.

    I have an unbounded task flow (usersTaskFlow), in where I have a route and two fragments.

    In the home page, I dropped the usersTaskFlow. With this, I can control what fragment will be shown depending on the user Role.

    In the fragment, I have an af:query and an af:table to show the results, the af:query depends on a Search Criteria defined in my VO.

    So, the user can search for the 3 fields with LOVs, configured like Input Text with List Of Values.

    So, I need to know, how can I assign the same parameter for all my View Objects automatically.

    I mean, my main filter here is the logged user role, this data is not selectable in the app. So, this must be done transparently for the user.

    I will appreciate so much your comments.


  • Zeeshan Thursday, November 12, 2020
    Hi Shay,
    I have an issue in this demo. You typed M% for department name and you select first one "Marketing" but there is "Manufacturing" too if you select 2nd then lov automatically populate the id of only marketing. why so?
    I tried this only happens when both VO's based on SQL Query.
  • Shay Thursday, November 12, 2020
    Best place to post questions is the JDev forum:
    Provide info on what you are aiming to do and what goes wrong there.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.