ADF Query with Parameters and List of Values

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:

Comments:

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.

Posted by Sumitha on October 10, 2010 at 11:02 PM PDT #

Sumitha, there is a section showing how to create a query with bind parameters here: http://st-curriculum.oracle.com/obe/jdev/obe11jdev/ps1/ria_application/developriaapplication_long.htm#ah1

Posted by shay.shmeltzer on October 11, 2010 at 01:04 AM PDT #

Brilliant..!!! Thank you very much Shay.

Posted by Sumitha on October 11, 2010 at 08:56 PM PDT #

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?

Posted by Sumitha on October 12, 2010 at 07:50 PM PDT #

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

Posted by shay.shmeltzer on October 13, 2010 at 04:59 AM PDT #

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->' similarly, 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... -Thanks

Posted by Santosh Vaza on December 27, 2010 at 12:17 PM PST #

Santosh, check out this new blog entry: http://blogs.oracle.com/shay/2010/12/combining_multiple_queries_and.html

Posted by shay.shmeltzer on December 28, 2010 at 02:36 AM PST #

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

Posted by guest on July 07, 2011 at 11:11 PM PDT #

guest, try using NVL in your query:
employee_id=nvl(:p_id,employee_id)

Posted by Shay on July 08, 2011 at 02:24 AM PDT #

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

Posted by guest on July 11, 2011 at 05:10 PM PDT #

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

Posted by Shay on July 12, 2011 at 04:35 AM PDT #

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

Thanks

Posted by guest on July 29, 2011 at 08:31 PM PDT #

guest - Here is how you do it with the query component:
http://blogs.oracle.com/shay/2011/01/dependent_lovs_in_an_afquery_c.html
It might also work for your scenario - though I haven't tested this yet.

Posted by Shay on August 01, 2011 at 04:30 AM PDT #

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

Cheers,
Caddick.

Posted by Caddick on November 15, 2011 at 09:44 PM PST #

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.

Posted by shay on November 17, 2011 at 03:43 AM PST #

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

Posted by Sandi on March 16, 2012 at 08:12 AM PDT #

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

Posted by Niels on May 30, 2012 at 12:40 AM PDT #

I have a related issue (jdev 11.1.2.2).

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

Posted by Niels on May 30, 2012 at 03:58 AM PDT #

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

Posted by Niels on May 31, 2012 at 06:51 AM PDT #

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:

http://st-curriculum.oracle.com/obe/jdev/obe11jdev/ps1/ria_application/developriaapplication_long.htm#ah1

Posted by NewADFDev on November 29, 2012 at 05:24 PM PST #

NewADFDev, not sure which video you are referring to, but I would recommend you start from the ADF Insider videos:
http://www.oracle.com/technetwork/developer-tools/adf/overview/adf-mobile-096323.html

Posted by Shay on December 03, 2012 at 03:50 PM PST #

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

Posted by shiko on May 14, 2013 at 04:01 PM PDT #

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:
https://blogs.oracle.com/shay/entry/adding_an_lov_to_a_query_param

Posted by Shay on May 14, 2013 at 04:54 PM PDT #

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 11.1.2.4 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

Posted by shiko on May 15, 2013 at 03:15 AM PDT #

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.

Posted by guest on November 15, 2013 at 03:19 AM PST #

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

Posted by Shay on November 15, 2013 at 10:52 AM PST #

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?

Posted by guest on November 16, 2013 at 12:41 AM PST #

Hi,
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.

Thanks

Posted by s@ud on November 17, 2013 at 10:44 AM PST #

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

Thanks

Posted by guest on January 21, 2014 at 03:32 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

me
I'm a Director of Product Management for the Oracle Java Development Tools.
Follow me:
Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today