Shay Shmeltzer's Oracle Development Tools Tips

  • December 3, 2009

ADF Query with Parameters and List of Values

Shay Shmeltzer
Director of Product Management - Oracle

This one is probably for ADF beginners, but it is a very common use case - so having a little demo can never hurt.

The goal is to enable the user to select a few values from drop down lists and based on these values filter the results of a query and display them in a table.

In our case we want to filter employees by the department they work in and the salary they have. And we want to show a list of departments that the user can choose from.

All you need to do is create an ADF BC view object with bind parameters, and then in your page drag the parameters one by one to the page to drop them as select lists.

See this demo:

Join the discussion

Comments ( 31 )
  • Sumitha Monday, October 11, 2010
    This video is exactly for what I am looking for. But Pls tell me how to create an ADF BC view object with bind parameters? It shows error when I define the where clause with the bind variables.
  • shay.shmeltzer Monday, October 11, 2010
    Sumitha, there is a section showing how to create a query with bind parameters here:
  • Sumitha Tuesday, October 12, 2010
    Brilliant..!!! Thank you very much Shay.
  • Sumitha Wednesday, October 13, 2010
    Hi Shay,
    I could replicate this functionality as you have described. Further, I gave a default value to the bind variable, therefore, as soon as I run the page, I get the table according to the default value of the bind variable.
    Then, when I select one value from the choice list, the table is filtered based on that value.
    But how can I get back to the default value again after that?
  • shay.shmeltzer Wednesday, October 13, 2010
    You can write a method in the execute button that will set the values of the parameter binding again.
    see http://download.oracle.com/docs/cd/E14571_01/web.1111/b31974/web_adv.htm#CACDJCFE
  • Santosh Vaza Monday, December 27, 2010
    Hi Shay,
    I am having a page with one field called 'Folio Number' and a submit button.
    On that page i have 2 tables both uses different database tables.
    the query of the two tables is like
    select * from table1 where filio_no='<-field value of folio number on form->'
    select * from table2 where filio_no='<-field value of folio number on form->'
    So how can i do this, can you provide me some links so that i can do this...
  • shay.shmeltzer Tuesday, December 28, 2010
    Santosh, check out this new blog entry:
  • guest Friday, July 8, 2011

    As explained in above video, if i dont pass any parameters in UI, i want all of my records to be displayed But i am not getting any records

    If i dont pass any params i want all of my records to come so i cant set to a particular default value

    How can we do this

  • Shay Friday, July 8, 2011

    guest, try using NVL in your query:


  • guest Tuesday, July 12, 2011

    that was my mistake... i didnt gave parameter as required... thanks for that... i have one more query i have used from and to date parameters in my query on a column created_date..

    1. Now when i dragged and dropped my bind variables it is working fine

    I want a validation to be imposed like from date shouldn't be less than to date how can we do this for the bind variables which we dragged and dropped in search criteria

    2. And also when i run my jspx the whole data is getting displayed before i press the execute with params button ... how to prevent the execution of query before search is performed

  • Shay Tuesday, July 12, 2011

    guest, for 1 you can add a validator to the textItem that represent your parameter and code the check in there.

    2. See this blog entry: http://blogs.oracle.com/shay/2010/08/delay_method_execution_when_us.html

  • guest Saturday, July 30, 2011

    Hello Sir

    I dragged and dropped variables from execute with params and as explained in the video when i did it that way everything is working fine

    But i want cascading to be done for my bind variables which i dropped

    In my vo i see there are no view accessors added even though i have binded my bind variables to a vo to get the lov

    How to apply this cascading effect


  • Shay Monday, August 1, 2011

    guest - Here is how you do it with the query component:


    It might also work for your scenario - though I haven't tested this yet.

  • Caddick Wednesday, November 16, 2011

    I have created one LOV using DataControl , Now I wanted to set some value as Default value. Currently , I have inserted one row in table with value "Browse to Select" , but I wanted to replace this value using code.

    Can you please help me out...I am not able to see this video...please help me out...



  • shay Thursday, November 17, 2011

    Caddick, did you try setting a default value for the field that will end up using the LOV.

    So for example if you have a LOV of jobs that is used for the JOB_ID field in the Emp table, set the default value for the jobid field in the emp table.

  • Sandi Friday, March 16, 2012

    Hi shay !

    First of all great post, but i noticed that when you select something from the list, the First blank row dissapears, so you cant actually query the whole table and get all of the records again.

    Have you tho. about that yet ? Got any solution ? Im experimenting with Include Blank Item on the lists, but no luck :/

    Regards, Sandi

  • Niels Wednesday, May 30, 2012

    Hi All,

    I encounter kind of the same issue as Sandi ..

    Initially the lov on my binding variable displays a blank row (although i have set "No selected Item" to "selection required"). After first selection only the valid choices, and no blank row, are displayed.

    Is this due to a missing "execute-query-on-LOV" or something similar? Have you got any ideas about how to avoid the blank row?

    Thanks in advance

    Brgds Niels

  • Niels Wednesday, May 30, 2012

    I have a related issue (jdev

    For "filtering" rows shown in a table I'm using a LOV on a bind variable.

    I want to (but cannot) get rid of the initial blank row alltogether.

    Actually I want the first row to be shown in the LOV. After first manual selection it works the way I want it.

    I have tried to include Hint="showAndExecute", but it seems to apply only to a ListBinding, and not to the IterBinding that I have.

    Any ideas to get rid of the blank row?

    Thanks in advance,


  • Niels Thursday, May 31, 2012

    Sorry for double-posting ...

    I have worked on with this, and realized that the problem may be of a different nature.

    In the VO there is a viewCriteria, and of course a bind variable. Until now I have not specified anything for "Value" (there are the fields name, type, value and info). Now i see, that if I do set a value that is matched by an actual row (e.g. id=2) I don't get the blank line. Leaving this at the default (NULL) or setting it to a non-existent id, a blank line is shown.

    While it is do-able to set a value it is not practical because I cannot guarantee that a given Id will always exist. I have been working along the lines of setting a value in a bean, but this is not done yet.

    I hope somebody can use this :-)

    Best regards Niels

  • NewADFDev Friday, November 30, 2012

    Hi shay: I would like to see your video to create the ADF BC, but this link does not work. Can you please update the link or correct the link:


  • Shay Monday, December 3, 2012

    NewADFDev, not sure which video you are referring to, but I would recommend you start from the ADF Insider videos:


  • shiko Tuesday, May 14, 2013

    Many thanks to you for your helpful posts

    please how i can make dependent LOVs on Parameters using

    execute with parameter search not af:Query i know you have post

    about doing this with this component and search criteria

    thank you

  • Shay Tuesday, May 14, 2013

    I think that you'll need to define two separate LOVs and then use partial page refresh to have one depend on the other.

    Here is how you add an LOV to an execute with param parameter field:


  • shiko Wednesday, May 15, 2013

    thank you shay

    but when trying to do your example i face 2 things

    1- i can't convert parameter to input text with lov by your way which you made it at the video my jdeveloper version is and it seems it not allowed to this version and i tried to do it manually by making component input text with lov and copy value from parameter to it and so on

    2- when i ran the page i got error

    also i need to add that i need list in compo box shape but all what i need to make it dependent

    thank you again

  • guest Friday, November 15, 2013

    ExecutewithParam is not working properly for me when used as SelectOneChoice. I have three parameters in search form in which 2 parameters has been made as SelectOneChoice and last one as text. Resulting shown in table are changing based on the text parameter value but no change happens when u change selection in selectoneChoice dropdown. Surprisingly, when I change the selectonechoice to text, it works perfect. I am using 12c. Pls advice.

  • Shay Friday, November 15, 2013

    guest - you should check your partialTrigger setting and autosubmit between the table and the fields that control it such as the selectOneChoices.

  • guest Saturday, November 16, 2013

    Thanks for the explanation. But am not using partial submit but uses adf action button created by dragging ExecuteWithParam operation from data control. Basically an view has been created with 3 parameters. A jsf page to display the view as table and a search facility by dragging all 3 parameters from view object data control operation(2 parameters as SelectOneCHoice and one parameter as textbox). The page works perfect when we give different values to text box and click search button. But there is no change happens when we make changes in other two params(select box) and click search button. It seems the value from selectOneChoice is not passed when clicking search button. What to do?

  • s@ud Sunday, November 17, 2013


    Even I am facing the same issue working with combo box search parameters. I have a bind variable pPersId which I have dragged onto my page and created a Select One choice component. But when i click on ExecuteWithParams, for some reasons the value of this bind variable is set as null. The text field parameters are working alright.

    While enabling the diagnostic log I found these messages:

    --> Update target row:null with null value from list binding:pPersId

    --> XdmTasksView1 ViewRowSetImpl.doSetWhereClause(-1, pPersId, null)

    --> Binding null of type 12 for "pPersId"

    Any pointers to resolve this would be of great help.


  • guest Tuesday, January 21, 2014

    Hi Shay

    Thanks a lot for the post I tried creating the search form using the executewithparams and have 11 bind variables , the VO is created based on a very complex sql query which is accessing 4-5 tables,

    I have dropped the executewithparams operation as a command from the DC and the VO as read only table , but problem is on page load the table shows up result even with out clicking the command button , how can i display the result in table only on click of the button ?

    Please help


  • pascale Monday, March 2, 2015


    executewithparams is working fine, but i cannot use the table filter with it. any solution? N.B: i dont want to used the view criteria

  • Chinmayee Tuesday, March 5, 2019
    Hi Shay,

    I am using jdev 12c, i am not able to find list of values oprion in control types drop down list. How to implement
    LOV in view criteria bind parameter please let me know.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.