adfemg
  1. adfemg
  2. ADFEMG-14

Input List Of Values, autosuggest behavior

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Incomplete
    • Labels:
      None
    • Environment:

      jdeveloper 11.1.1.2 - 11.1.1.6

      Description

      JDeveloper version : ALL
      WebLogic Server version : n.a.
      impact level : high
      why : annoying to users and performance implications
      summary :
      I would like to post about an issue i am straggling with oracle support since jdeveloper 11.1.1.2.
      It is SR 3-5035735611 and referenced Bug 9316480: LOV AUTOCOMPLETE USES LIKE OPER FOR NUMBER, CAUSING FULL TABLE SCAN

      Description:
      By default when you type a value in an input list of values the query is excecuted always with the LIKE operator.

      SELECT Departments.DEPARTMENT_ID,
      Departments.DEPARTMENT_NAME,
      Departments.MANAGER_ID,
      Departments.LOCATION_ID
      FROM DEPARTMENTS Departments WHERE
      (((Departments.DEPARTMENT_ID LIKE ( :vc_temp_1 || '%'))))

      And even if the value you entered is valid the LOV popup will open showing all values starting with the value you entered.
      so in the attached test cases if you type '10' in the departmentId the LOV will open showing 10, 100, 101 etc.

      Impact:
      This is very annoying for users that do heads-down data entry. They make change requests for us to create custom made popups for LOVs.
      Also if the field is a number (like the departmentId) the executed query force full table scan even though it is the primary key.
      In Large List of values this causes performance issues.
      I understand this functionality is designed for the auto-suggest behavior, yet there is no way to override it.

      Proposal:
      We just need the option to disable auto-suggest behavior (on design time) so that the query is executed with '=' operator and the LOV does not open when the value entered is valid.

        Activity

        Hide
        chriscmuir added a comment -

        Relating bug 8983396 fixed 11.1.1.3.0.

        Show
        chriscmuir added a comment - Relating bug 8983396 fixed 11.1.1.3.0.
        Hide
        chriscmuir added a comment -

        Last internal comment has a fix scheduled for 12.1.3.0.0:

        01/06/12 01:52 pm *** (CHG: FixBy->12.1.3.0.0)

        Show
        chriscmuir added a comment - Last internal comment has a fix scheduled for 12.1.3.0.0: 01/06/12 01:52 pm *** (CHG: FixBy->12.1.3.0.0)
        Hide
        chriscmuir added a comment -

        At the moment this issue has been requested by 3 different customers. If more customers request a fix I suggest this will bump it's priority.

        Show
        chriscmuir added a comment - At the moment this issue has been requested by 3 different customers. If more customers request a fix I suggest this will bump it's priority.
        Hide
        mkoniotakis added a comment -

        Thanks for the update.
        Our application is a product deployed to many customers that they ask us, for a fix.
        Should we ask them to add it in their oracle support to bump it's priority?

        I fill the number of the customer's asking for the same issue is a poor criterion to decide the priority.

        Show
        mkoniotakis added a comment - Thanks for the update. Our application is a product deployed to many customers that they ask us, for a fix. Should we ask them to add it in their oracle support to bump it's priority? I fill the number of the customer's asking for the same issue is a poor criterion to decide the priority.
        Hide
        chriscmuir added a comment -

        Hi Michael

        "I fill the number of the customer's asking for the same issue is a poor criterion to decide the priority."

        Let's be clear though, that criterion is mine, not Oracle's. No insult taken on my part

        Let's discuss my strategy. Oracle and its current Support and development practices are entrenched. I haven't even bothered to decide to take this on and affect change from the top down. With 106000 employees, and hundreds if not thousands of processes, the EMG, me or you are not going to affect any substantial and significant change.

        Instead my goal is to work at a much smaller level for customers to try and help customers get through the hurdle of seeking improvements for (my specific interest) ADF. And in some cases that will be a customer per customer, issue per issue engagement. For each issue my efforts will attempt to take an intelligent assessment of the lie of the land of the issue (by example looking at the progress of the SR/bug) and then make a recommendation on what I think can work to get the issue to progress.

        So with your specific SR/bug I noted that:

        1) Issue was first logged by Steve Muench 26th Jan 2010
        2) Over 2010 the issue had a fair amount of activity (even though that was a full year)
        3) The issue then was parked for most of 2011

        • at this point the issue had not been scheduled for any fix or new features -

        4) In late 2011 another customer logged against the bug
        5) In very late 2011 the ADF EMG was also logged against the bug thanks to something you posted

        It appears to me it was only after #4 and #5 was the issue scheduled for a fix/new feature.

        On evidence if it wasn't scheduled for a fix until more customers logged against it and there was activity after a year, what I want to do is try that again. Let's get more customers to log against it. Once I've a few more logged I'll go back to the development manager and have a discussion with him to bump the priority.

        Now I don't know if I'll be successful but the important point is I'm willing to try on your behalf.

        So you have a choice. We, you, I, Frank and the EMG members can try and work as a team to get a successful outcome for EMG members. Or you can continue with Oracle Support.

        I hope you'll work with us to make this a success.

        Regards,

        CM.

        Show
        chriscmuir added a comment - Hi Michael "I fill the number of the customer's asking for the same issue is a poor criterion to decide the priority." Let's be clear though, that criterion is mine, not Oracle's. No insult taken on my part Let's discuss my strategy. Oracle and its current Support and development practices are entrenched. I haven't even bothered to decide to take this on and affect change from the top down. With 106000 employees, and hundreds if not thousands of processes, the EMG, me or you are not going to affect any substantial and significant change. Instead my goal is to work at a much smaller level for customers to try and help customers get through the hurdle of seeking improvements for (my specific interest) ADF. And in some cases that will be a customer per customer, issue per issue engagement. For each issue my efforts will attempt to take an intelligent assessment of the lie of the land of the issue (by example looking at the progress of the SR/bug) and then make a recommendation on what I think can work to get the issue to progress. So with your specific SR/bug I noted that: 1) Issue was first logged by Steve Muench 26th Jan 2010 2) Over 2010 the issue had a fair amount of activity (even though that was a full year) 3) The issue then was parked for most of 2011 at this point the issue had not been scheduled for any fix or new features - 4) In late 2011 another customer logged against the bug 5) In very late 2011 the ADF EMG was also logged against the bug thanks to something you posted It appears to me it was only after #4 and #5 was the issue scheduled for a fix/new feature. On evidence if it wasn't scheduled for a fix until more customers logged against it and there was activity after a year, what I want to do is try that again. Let's get more customers to log against it. Once I've a few more logged I'll go back to the development manager and have a discussion with him to bump the priority. Now I don't know if I'll be successful but the important point is I'm willing to try on your behalf. So you have a choice. We, you, I, Frank and the EMG members can try and work as a team to get a successful outcome for EMG members. Or you can continue with Oracle Support. I hope you'll work with us to make this a success. Regards, CM.
        Hide
        mkoniotakis added a comment -

        Thanks Chris,

        I am with you all the way.

        It is just that in the SR i had a similar comment that this issue is Highly unlikely to be fixed since only 2 customers are asking for it.

        Yet since issues and requests increase, i understand that prioritisation criteria as required.
        We do the same thing

        On our first and second customer that we migrated from Forms to ADF that raised this issue, we managed to convince them to live with it.
        Now the third (and largest) customer threatens not to migrate unless we fix such issues.
        so for such LOVs we expose view object to application module and create custom search popup with javascripts to simulate the LOV behavior.
        This costs 10 times more than the default LOV development, in order to give them functionality similar to what they had in Forms.

        I guess not many customers use ADF to create heavy dataentry pages, or they design it diferently (without large LOV usage).

        Still in the criteria of prioritisation i think it is also important If it has or not
        an easy workaround
        performance implication
        usability effect
        loss of existing functionality

        Thanks again for your effort here, i will support it aa much as i can.

        Show
        mkoniotakis added a comment - Thanks Chris, I am with you all the way. It is just that in the SR i had a similar comment that this issue is Highly unlikely to be fixed since only 2 customers are asking for it. Yet since issues and requests increase, i understand that prioritisation criteria as required. We do the same thing On our first and second customer that we migrated from Forms to ADF that raised this issue, we managed to convince them to live with it. Now the third (and largest) customer threatens not to migrate unless we fix such issues. so for such LOVs we expose view object to application module and create custom search popup with javascripts to simulate the LOV behavior. This costs 10 times more than the default LOV development, in order to give them functionality similar to what they had in Forms. I guess not many customers use ADF to create heavy dataentry pages, or they design it diferently (without large LOV usage). Still in the criteria of prioritisation i think it is also important If it has or not an easy workaround performance implication usability effect loss of existing functionality Thanks again for your effort here, i will support it aa much as i can.
        Hide
        chriscmuir added a comment -

        Thanks!

        Show
        chriscmuir added a comment - Thanks!
        Hide
        Jan Vervecken added a comment -

        note : using the same TestLOVAutoSuggest.rar from this JIRA issue ADFEMG-14 in JIRA issue ADFEMG-20

        Show
        Jan Vervecken added a comment - note : using the same TestLOVAutoSuggest.rar from this JIRA issue ADFEMG-14 in JIRA issue ADFEMG-20
        Hide
        Jan Vervecken added a comment -

        hi

        Maybe the following idea for a potential workaround could be considered, related to the description in this this JIRA issue ADFEMG-14 about "... and the LOV does not open when the value entered is valid."?

        What if the List of Values List Data Source would be based on a View Object that has a SQL statement that only returns the exact matching row when it exists, or all the valid rows when no exact match exists?

        Maybe something like this:

        select dep.department_id, dep.department_name, dep.manager_id, dep.location_id
        from (select count(*) unique_count from departments where (department_id = :DepartmentIdBVar)) uc_dep, departments dep
        where (uc_dep.unique_count = 0) or (department_id = :DepartmentIdBVar)
        

        This would require the entered value to be known in the SQL statement, and for that overriding executeQueryForCollection() might be sufficient:

        protected void executeQueryForCollection(Object pQc, Object[] pParams, int pNoUserParams)
        {
        	final String VC_TEMP_1_VAR_NAME = "vc_temp_1";
        	final String FBK_KY__0_VAR_NAME = "fbkKy__0";
        	final String DEPARTMENT_ID_BVAR_VAR_NAME = "DepartmentIdBVar";
        	Object[] vParams = pParams;
        	int vNoUserParams = pNoUserParams;
        	if (pParams != null)
        	{
        		vParams = new Object[pParams.length + 1];
        		Object vVcTemp1 = null;
        		Object vFbkKy0 = null;
        		Object vDepartmentIdBVar = null;
        		for (int i = 0; i < pParams.length; i++)
        		{
        			Object[] vParamNV = (Object[])pParams[i];
        			vParams[i] = vParamNV;
        			Object vParamName = vParamNV[0];
        			Object vParamValue = vParamNV[1];
        			if (VC_TEMP_1_VAR_NAME.equals(vParamName))
        			{
        				vVcTemp1 = vParamValue;
        			}
        			if (FBK_KY__0_VAR_NAME.equals(vParamName))
        			{
        				vFbkKy0 = vParamValue;
        			}
        			if (DEPARTMENT_ID_BVAR_VAR_NAME.equals(vParamName))
        			{
        				vDepartmentIdBVar = vParamValue;
        			}
        		}
        		if ((vDepartmentIdBVar == null) && (vVcTemp1 != null))
        		{
        			vParams[(vParams.length - 1)] = new Object[]{DEPARTMENT_ID_BVAR_VAR_NAME, vVcTemp1};
        			vNoUserParams = pNoUserParams + 1;
        		}
        		else if ((vDepartmentIdBVar == null) && (vFbkKy0 != null))
        		{
        			vParams[(vParams.length - 1)] = new Object[]{DEPARTMENT_ID_BVAR_VAR_NAME, vFbkKy0};
        			vNoUserParams = pNoUserParams + 1;
        		}
        		else if (vDepartmentIdBVar == null)
        		{
        			vParams[(vParams.length - 1)] = new Object[]{DEPARTMENT_ID_BVAR_VAR_NAME, null};
        			vNoUserParams = pNoUserParams + 1;
        		}
        		else
        		{
        			vParams = pParams;
        			vNoUserParams = pNoUserParams;
        		}
        	}
        	else
        	{
        		vParams = new Object[][]{{DEPARTMENT_ID_BVAR_VAR_NAME, null}};
        		vNoUserParams = 1;
        	}
        	super.executeQueryForCollection(pQc, vParams, vNoUserParams);
        }
        

        Not sure if this covers all the usages of the component, but it is an idea that might be worth reviewing.

        Note that such approach is also not very future proof, as it would require more explicit review for each future release of the framework (but it could provide a "workaround" with existing components and features).

        regards
        Jan Vervecken

        Show
        Jan Vervecken added a comment - hi Maybe the following idea for a potential workaround could be considered, related to the description in this this JIRA issue ADFEMG-14 about "... and the LOV does not open when the value entered is valid."? What if the List of Values List Data Source would be based on a View Object that has a SQL statement that only returns the exact matching row when it exists, or all the valid rows when no exact match exists? Maybe something like this: select dep.department_id, dep.department_name, dep.manager_id, dep.location_id from (select count(*) unique_count from departments where (department_id = :DepartmentIdBVar)) uc_dep, departments dep where (uc_dep.unique_count = 0) or (department_id = :DepartmentIdBVar) This would require the entered value to be known in the SQL statement, and for that overriding executeQueryForCollection() might be sufficient: protected void executeQueryForCollection( Object pQc, Object [] pParams, int pNoUserParams) { final String VC_TEMP_1_VAR_NAME = "vc_temp_1" ; final String FBK_KY__0_VAR_NAME = "fbkKy__0" ; final String DEPARTMENT_ID_BVAR_VAR_NAME = "DepartmentIdBVar" ; Object [] vParams = pParams; int vNoUserParams = pNoUserParams; if (pParams != null ) { vParams = new Object [pParams.length + 1]; Object vVcTemp1 = null ; Object vFbkKy0 = null ; Object vDepartmentIdBVar = null ; for ( int i = 0; i < pParams.length; i++) { Object [] vParamNV = ( Object [])pParams[i]; vParams[i] = vParamNV; Object vParamName = vParamNV[0]; Object vParamValue = vParamNV[1]; if (VC_TEMP_1_VAR_NAME.equals(vParamName)) { vVcTemp1 = vParamValue; } if (FBK_KY__0_VAR_NAME.equals(vParamName)) { vFbkKy0 = vParamValue; } if (DEPARTMENT_ID_BVAR_VAR_NAME.equals(vParamName)) { vDepartmentIdBVar = vParamValue; } } if ((vDepartmentIdBVar == null ) && (vVcTemp1 != null )) { vParams[(vParams.length - 1)] = new Object []{DEPARTMENT_ID_BVAR_VAR_NAME, vVcTemp1}; vNoUserParams = pNoUserParams + 1; } else if ((vDepartmentIdBVar == null ) && (vFbkKy0 != null )) { vParams[(vParams.length - 1)] = new Object []{DEPARTMENT_ID_BVAR_VAR_NAME, vFbkKy0}; vNoUserParams = pNoUserParams + 1; } else if (vDepartmentIdBVar == null ) { vParams[(vParams.length - 1)] = new Object []{DEPARTMENT_ID_BVAR_VAR_NAME, null }; vNoUserParams = pNoUserParams + 1; } else { vParams = pParams; vNoUserParams = pNoUserParams; } } else { vParams = new Object [][]{{DEPARTMENT_ID_BVAR_VAR_NAME, null }}; vNoUserParams = 1; } super .executeQueryForCollection(pQc, vParams, vNoUserParams); } Not sure if this covers all the usages of the component, but it is an idea that might be worth reviewing. Note that such approach is also not very future proof, as it would require more explicit review for each future release of the framework (but it could provide a "workaround" with existing components and features). regards Jan Vervecken
        Hide
        mkoniotakis added a comment -

        Thank you Jan very much for the suggestion.
        It is an interesting approach.

        A workaround that we found lately that might be more future proof and does not require change in each view object is the following:

        In LaunchPopupListener of LOV we check if the entered value exist in LOV and we don't lauch the popup
        It also need to reset the LOV criteria so that next time it opens it does not keep the previous value.
        A sample code is:

        public void reqProviderLaunchPopupListener(LaunchPopupEvent launchPopupEvent) {
        Object submittedValue = launchPopupEvent.getSubmittedValue();

        Row byKeyString = valueExistsInReqProviderLov(submittedValue);

        if(byKeyString!=null)

        { launchPopupEvent.setLaunchPopup(false); ADFUtils.setBoundAttributeValue("RequesterProviderId", submittedValue.toString().toUpperCase()); this.getReqProvider().setSubmittedValue(submittedValue.toString().toUpperCase()); AdfFacesContext.getCurrentInstance().addPartialTarget(this.getReqProvider()); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); listOfValuesModel.resetCriteria(); }

        }

        /**

        • Helper method that checks if key exists in list iterator.
        • @param value
        • @return
          */
          private Row valueExistsInReqProviderLov(Object value){
          Key key = new Key(new Object[] { value.toString().toUpperCase() }

          );

        FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null;
        listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel();
        JUCtrlListBinding ctrlListBinding = null;
        ctrlListBinding = (JUCtrlListBinding)ADFUtils.findCtrlBinding("RequesterProviderId");
        DCIteratorBinding iter = ctrlListBinding.getListIterBinding();
        iter.executeQuery();

        Row byKeyString = iter.findRowByKeyString(key.toStringFormat(true));

        return byKeyString;
        }

        Show
        mkoniotakis added a comment - Thank you Jan very much for the suggestion. It is an interesting approach. A workaround that we found lately that might be more future proof and does not require change in each view object is the following: In LaunchPopupListener of LOV we check if the entered value exist in LOV and we don't lauch the popup It also need to reset the LOV criteria so that next time it opens it does not keep the previous value. A sample code is: public void reqProviderLaunchPopupListener(LaunchPopupEvent launchPopupEvent) { Object submittedValue = launchPopupEvent.getSubmittedValue(); Row byKeyString = valueExistsInReqProviderLov(submittedValue); if(byKeyString!=null) { launchPopupEvent.setLaunchPopup(false); ADFUtils.setBoundAttributeValue("RequesterProviderId", submittedValue.toString().toUpperCase()); this.getReqProvider().setSubmittedValue(submittedValue.toString().toUpperCase()); AdfFacesContext.getCurrentInstance().addPartialTarget(this.getReqProvider()); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); listOfValuesModel.resetCriteria(); } } /** Helper method that checks if key exists in list iterator. @param value @return */ private Row valueExistsInReqProviderLov(Object value){ Key key = new Key(new Object[] { value.toString().toUpperCase() } ); FacesCtrlLOVBinding.ListOfValuesModelImpl listOfValuesModel = null; listOfValuesModel = (FacesCtrlLOVBinding.ListOfValuesModelImpl)this.getReqProvider().getModel(); JUCtrlListBinding ctrlListBinding = null; ctrlListBinding = (JUCtrlListBinding)ADFUtils.findCtrlBinding("RequesterProviderId"); DCIteratorBinding iter = ctrlListBinding.getListIterBinding(); iter.executeQuery(); Row byKeyString = iter.findRowByKeyString(key.toStringFormat(true)); return byKeyString; }
        Hide
        leond added a comment -

        Hi

        We are busy testing the code supplied above, but have encountered some inconsistencies. Will give feedback next week.

        I am not pro having workarounds, especially when fixes are imminent and you have to backtrack and remove redundant code!

        We have also hit this scenario and it really does not go down well with our users. Considering that our product table contains close on 500 000 records and when they manually capture product id 20, it displays all product id like '20%'.

        I have read Bug 8983396: EXTRA QUERIES EXECUTED FOR LOV AND PERFORMANCE ISSUES and it has a Fixed in Version entry, but the bug still persists.

        We will log another SR and reference the above mentioned SR and hope this add impetus to a future fix. Until then I will keep the users at bay!

        Leon.

        Show
        leond added a comment - Hi We are busy testing the code supplied above, but have encountered some inconsistencies. Will give feedback next week. I am not pro having workarounds, especially when fixes are imminent and you have to backtrack and remove redundant code! We have also hit this scenario and it really does not go down well with our users. Considering that our product table contains close on 500 000 records and when they manually capture product id 20, it displays all product id like '20%'. I have read Bug 8983396: EXTRA QUERIES EXECUTED FOR LOV AND PERFORMANCE ISSUES and it has a Fixed in Version entry, but the bug still persists. We will log another SR and reference the above mentioned SR and hope this add impetus to a future fix. Until then I will keep the users at bay! Leon.
        Hide
        chriscmuir added a comment -

        Leon, can you confirm the new SR number please?

        CM.

        Show
        chriscmuir added a comment - Leon, can you confirm the new SR number please? CM.
        Hide
        chriscmuir added a comment -

        Without a new SR/bug to reference I'll close this issue in 1 week.

        Show
        chriscmuir added a comment - Without a new SR/bug to reference I'll close this issue in 1 week.
        Hide
        chriscmuir added a comment -

        Issue closed, no further information supplied.

        Show
        chriscmuir added a comment - Issue closed, no further information supplied.

          People

          • Assignee:
            chriscmuir
            Reporter:
            mkoniotakis
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: