0 Replies Latest reply: Aug 4, 2014 11:46 PM by Sruthi Tamiri RSS

    APEX_ITEM.POPUPKEY_FROM_QUERY

    Sruthi Tamiri

      Hi team,

       

          In our existing application a screen is developed uisng dynamic concepts [APEX_ITEMS], the problem we are having is displaying the screen much takes time.

          recently our applications were migrtaed to 4.2 version & 11g database,

          There is a strange scenerio we had addresses as mention below

       

      1. Team have used APEX_ITEM.SELECT_LIST_FROM_QUERY_XL, which works fine when data is more than 5000+ records in drop list in 10g instance, but same problem is having 11g it takes

          nearly 5 min to load the page, where as in 10g it takes 1-1.5 min to load the page.

       

      2. We are not able to trace what could be problem.

       

      3. We are trying to implement uisng APEX_ITEM.POPUPKEY_FROM_QUERY, but when page loads we would like to display the default/first occurnace value for that value, but we are not able to

          do the same.

       

      4. As per the API provided, we have to display the column name to show the result, we have checked two approaches, but was not to mark. Could some one help us on this.

       

      5. From below if we observe that i am conserding local varibale as attrdescription and uisng that varibale in query 2.

       

      SELECT ATTR_DESCRIPTION into attrdescription FROM(
                         SELECT ATTR_DESCRIPTION,ATTR_CODE, ''2'' FROM T_NEW_ATTR tna, T_NEW_ATTR_TYPE tnat 
                         WHERE tna.ATTR_ATCL_CODE = '''|| n.prcl_atcl_code || ''' AND tnat.ATRT_ATTR_TYPE='''||n.prcl_attr_type|| ''' 
                         AND upper(tna.attr_description)=''#NOT APPLICABLE''
                         and tna.attr_code=tnat.atrt_attr_code 
                          UNION ALL
                         SELECT ATTR_DESCRIPTION,ATTR_CODE, ''3'' FROM T_NEW_ATTR tna, T_NEW_ATTR_TYPE tnat 
                         WHERE tna.ATTR_ATCL_CODE = '''|| n.prcl_atcl_code || ''' AND tnat.ATRT_ATTR_TYPE='''||n.prcl_attr_type|| '''
                         AND upper(tna.attr_description)<>''#NOT APPLICABLE''
                         and tna.attr_code=tnat.atrt_attr_code                          
                         order by 3,1) where rownum=1;
      

       

      Query #2

       

      htp.p(APEX_ITEM.POPUPKEY_FROM_QUERY(
          11,
         attrdescription, --here i am trying to use local varibale parameter
              'SELECT ATTR_DESCRIPTION, ATTR_CODE FROM(
                         SELECT ATTR_DESCRIPTION,ATTR_CODE, ''2'' FROM T_NEW_ATTR tna, T_NEW_ATTR_TYPE tnat 
                         WHERE tna.ATTR_ATCL_CODE = '''|| n.prcl_atcl_code || ''' AND tnat.ATRT_ATTR_TYPE='''||n.prcl_attr_type|| ''' 
                         AND upper(tna.attr_description)=''#NOT APPLICABLE''
                         and tna.attr_code=tnat.atrt_attr_code 
                         UNION ALL
                         SELECT ATTR_DESCRIPTION,ATTR_CODE, ''3'' FROM T_NEW_ATTR tna, T_NEW_ATTR_TYPE tnat 
                         WHERE tna.ATTR_ATCL_CODE = '''|| n.prcl_atcl_code || ''' AND tnat.ATRT_ATTR_TYPE='''||n.prcl_attr_type|| '''
                         AND upper(tna.attr_description)<>''#NOT APPLICABLE''
                         and tna.attr_code=tnat.atrt_attr_code                    
                         order by 3,1)',
         '45',
         '45',
         '',
         '',
         '',
         '',
         'YES',
         'id="f11_' || j || '"',
         'ATTR_DESCRIPTION')
         );
      

       

      During run time it selft it thorws as "From class must be declared and statement ignored."

       

      Regards,

      Sruthitamiri!!