Monday, November 30, 2009

Setting Query Dynamically in View Object. (Why in some cases even after using setQuery(), VO picks default query.)

Couple of time while making view object in OAF pages, you have conditions where instead of where clause or order by clause, you need to change the entire source of sql query of View Object.
There can be n number of such scenarios, for example you are using a querybean in a search page, and in a certain condition, you have to change your query, your VO is attached to a LOV and in certain condition you need the query to change in order to optimize the VO sql query etc. Following points always keep in mind while using setQuery():

1) The new query you want to set in the view object should have same column name and types as the original VO query.

2)As per OAF coding standards all coding related to setting where clause, order by clause or setQuery should be written in ViewObjectImpl class, so in such case, you should generate ViewObjectImpl class.

3)Most Important :Always call vo.setFullSqlMode(FULLSQL_MODE_AUGMENTATION) before calling setquery() on the view object.This ensures that the order by or the WHERE clause, that is generated by OA Framework, can be correctly
appended to your VO. The behavior with FULLSQL_MODE_AUGMENTATION is as follows:

a) The new query that you have programmatically set takes effect when you call setQuery and execute the query.
b) If you call setWhereClause or if a customer personalizes the criteria for your query region, BC4J augments the whereClause on the programmatic query that you set.

For example:
select * from (your programmatic query set through setQuery)
where (your programmatic where clause set through setWhereClause)
order by (your programmatic order set through setOrderBy)
The same query is changed as follows if a customer adds a criteria using personalization:
select * from (your programmatic query set through setQuery)
where (your programmatic where clause set through setWhereClause) AND
(additional personalization where clause)
order by (your programmatic order set through setOrderBy)_

Warning: If you do not set FULLSQL_MODE_AUGMENTATION, the whereClause and/or the
orderBy, which was set programmatically, will not augment on the new query that you set using setQuery. It will instead augment on your design time VO query.


Due to timing issues, always use the controller on the query region/LOV region while using setQuery().

21 comments:

Unknown said...

I tried to use vo.setFullSqlMode(FULLSQL_MODE_AUGMENTATION);

But my class does not compile .. am i missing something ?

Error(46,27): field FULLSQL_MODE_AUGMENTATION not found in class erco.oracle.apps.ap.oie.entry.header.webui.ercoHeaderKffCO

Mukul Gupta said...

Typically this code should go in VOimpl class , where FULLSQL_MODE_AUGMENTATION is defined in master class, so this code will work. Anyways, in CO use,
VOImpl.FULLSQL_MODE_AUGMENTATION, then this should compile.

Rudra. said...

Hi Mukul,

I have a requirement in HGRID where if parent row is checked, all child rows have to be dynamically checked and vice versa. I am able to achieve it after expanding the HGRID for first time. But before expanding even for once, I can not catch the child VO and its rows. How can I achieve this then?

Thanks,
Rudra

Rudra. said...

Pls ignore. I have got that. Thanks.

Arya said...
This comment has been removed by the author.
Arya said...

Even though included the function this.setFullSqlMode(FULLSQL_MODE_AUGMENTATION) , default query defined in the VO fires !

I have one independent and one dependent LOV . I have written the select query with out where clause as the default query ,and setWhereClause though code . The dependent LOV behaves as an independent LOV.

How can i get rid of this issue .

Thanks in advance,

Arya

sachin said...
This comment has been removed by the author.
sachin said...

Hi,
I am using the same process described above to set a dynamic query for my VO.
I have self link to the same VO in viewlink. I want to create a tree structure from the result set. But I can only see the first parents, no child is visible.

Does this something to do with the viewlink? Viewlink should work same as it does if query is specified in design mode, but it is not.

Thanks

Unknown said...

I have a similar requirement where I need to dynamically change VO's SQL for an LOV item.

Should I do that in CO - ProcesRequest Call using method implied in this post?

If so, can someone please either provide a sample or confirm it will work will be greatly appreciated.

Thanks
Deven

My World said...

hi mukul,
how can we reset query to view object after setquery

My World said...

hi mukul,
how can we reset query to view object after setquery

My World said...

hi mukul,
how can we reset query to view object after setquery

Unknown said...

Hi Mukul,
Thanks it is working fine but I'm facing one issue that after setting query how can we reset it to original query of view object.

Thanks,
Vivek

paulsmith198914@gmail.com said...

With the help of your article, magic happened:) I don't know how to express my gratitude to you, dear author! You’re doing a great job! By following dissertation editing services - edit-ing.services, you'll be able to contact with me - do it when you get the chance!

asgs said...

Awesome! This is pure ADF magic. Thanks a lot, Sir.

Unknown said...

I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog. 
rpa training in bangalore
best rpa training in bangalore
RPA training in bangalore
rpa course in bangalore
rpa training in chennai
rpa online training

suhas patil said...
This comment has been removed by the author.
deiva said...

Thanks it is working fine but I'm facing one issue that after setting query how can we reset it to original query of view object.
web designing training in chennai

web designing training in omr

digital marketing training in chennai

digital marketing training in omr

rpa training in chennai

rpa training in omr

tally training in chennai

tally training in omr

Jayalakshmi said...

Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting.
web designing training in chennai

web designing training in tambaram

digital marketing training in chennai

digital marketing training in tambaram

rpa training in chennai

rpa training in tambaram

tally training in chennai

tally training in tambaram

jeni said...

Its such as you learn my mind! You appeаr tо grasp ѕo much approximately this, such as you wrote the book in it or something.
I think that you could ɗo wіth some percent to pressure the mesѕage home a little bit,
but instead of that, this iѕ excellent blog. An excellent
read. I ԝilⅼ defіnitely be back.
oracle training in chennai

oracle training in velachery

oracle dba training in chennai

oracle dba training in velachery

ccna training in chennai

ccna training in velachery

seo training in chennai

seo training in velachery

shiny said...


I have read your blog its very attractive and impressive. I like it your blog.

web designing training in chennai

web designing training in annanagar

digital marketing training in chennai

digital marketing training in annanagar

rpa training in chennai

rpa training in annanagar

tally training in chennai

tally training in annanagar