7 Replies Latest reply: Jun 26, 2013 4:20 PM by 935660 RSS

    Limit for rows in a shuttle?

    blue72TA
      Hello All,

      Application Express 3.1.2.00.02
      IE 7

      I select 5000 rows (line ids) in my shuttle then press a button which passes the ids to a PL/SQL package to update a table using these line ids, this is something I've done in many applications. The problem in this case is only 4000 rows get updated. I receive no error when moving from the left to the right side of the shuttle or at any other time in the process. Everything seems to be just fine, until I look at the results and only 4000 rows were updated.

      Does anyone know what limit am I running into here? Is there a work around?

      Here is my code which includes the call to the PL/SQL package.

      Thanks!
      Declare
      
      l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
      lqtelntbl  FORX_QOT_PRICING.gQteLnTbl;
      x PLS_INTEGER := 1;
      
      BEGIN
      
      --
      -- Convert the colon separated string of values into
      -- a PL/SQL array
      
      If :P2_APPLY_TO_LINES = 'NA' Then
      
        RAISE_APPLICATION_ERROR( -20110, 'At least one line is required.' );
      
      End If;
      
      
      l_selected := APEX_UTIL.STRING_TO_TABLE(:P2_APPLY_TO_LINES);
      
      --
      -- Loop over array 
      --
      
      FOR i IN 1.. NVL(l_selected.count,0)
      LOOP
      
        If l_selected(i) != 'NA' Then
          lqtelnTbl(x) := l_selected(i);
          x := x+1;
        End If;
      
      END LOOP;
      
      FORX_QOT_PRICING.CostUpdate( pQteHdrId   => :P1_SEARCH,
                            pQteLnTbl   => lQteLnTbl,
                            pFieldName  => :P2_FIELD_NAME_SELECT,
                            pFieldValue => :P2_FIELD_VALUES,
                            pSupplierId => :P2_DEFAULT_SUPPLIER,
                            pSubInventory => :P2_SUBINVENTORY,
                            pClass      => :P2_CLASS,
                            pWhse       => :P2_WHSE,
                             -- begin JVM changes
                            pMaintLinkQuote  => :P2_MAINT_LINK_QUOTE,
                            pIncumbent       => :P2_INCUMBENT,
                            pEndOfLife       => :P2_END_OF_SERVICE_LIFE,
                            pMaintType       => :P2_MAINTAINENCE_TYPE2,
                            pContractStart   => :P2_CONTRACT_START_DATE,
                            pServiceDuration => :P2_SERVICE_DURATION,
                            pContractEnd     => :P2_CONTRACT_END_DATE,
                            pContractCoTerm  => :P2_CONTRACT_COTERM_DATE,
                            pMultiTermEndDate => :P2_MULTI_TERM_END_DATE,
                            --pPricingAnalyst  => :P2_PRICING_ANALYST,
                            --pMaintSpecialist => :P2_MAINTENANCE_SPECIALIST,
                            --pMaintTypeHead   => :P2_MAINTENANCE_TYPE1,
                            -- end JVM changes
                            pUserId     => :P1_USERID,
                            pRespId     => :P1_RESPID,
                            pRespApplId => :P1_RESPAPPLID );
      
      END;
        • 1. Re: Limit for rows in a shuttle?
          blue72TA
          ttt
          • 2. Re: Limit for rows in a shuttle?
            Kleber M-Oracle
            I know that there is a restriction on the size of shuttle items, due to issues in several Oracle engines - probably you are reaching the 32KB limit but I'm not sure why you wouldn't see an error, like in [http://application-express-blog.e-dba.com/?p=115]

            Are you sure your loop really sends the 5000 rows ahead for the procedure?

            Edited by: Kleber M on Mar 2, 2012 4:49 AM
            • 3. Re: Limit for rows in a shuttle?
              blue72TA
              Thanks for the reply. Here's what I know for sure...

              The shuttle (:P2_APPLY_TO_LINES) has 5000 rows (line ids) on the left side that are moved to the right. The page submits and calls my process, which calls a database package, no problems occur in the form of an error, but not all the rows that were in the right side shuttle get updated.

              In order to find out what's going on I figured I would determine the length of the delimited string in :P_APPLY_TO_LINES. I created a custom table with one column, datatype CLOB and inserted :P2_APPLY_TO_LINES into it as the first step in my process (before calling my database package). The length of this value is 32000. I then changed the shuttle LOV and used a different value (line number as opposed to line id) as the return value in the LOV definition. The shuttle item length still maxed out at 32000 when inserted into the CLOB column BUT more rows were processed by my procedure because more line numbers "fit" into :P2_APPLY_TO_LINES. This is because the max length of the line number is 4 digits in this case (lines 1-5000) while every line id is 7 digits.

              So I'm guessing there is a limit to the length of a shuttle item, and this limit is 32000. I'd just feel better if someone can confirm this, and I'm more confused by the fact that I don't get an error. You'd think if I tried to cram 40000 into a field that can only hold 32000 that I'd get an error. As it stands, if my users aren't paying attention they'll think they're updating all the rows when in reality they're only getting as many as :P2_APPLY_TO_LINES can hold.

              Does anyone have an opinion if my analysis is correct?

              Edited by: blue72TA on Mar 2, 2012 9:49 AM

              Edited by: blue72TA on Mar 2, 2012 9:50 AM
              • 4. Re: Limit for rows in a shuttle?
                Kleber M-Oracle
                I believe that in your Apex version, the product's jquery code used to build the contents of the item P2_APPLY_TO_LINE is restricting it to the 32K limit, to prevent the error when running the PL/SQL code in your process or submitting the page. Developers probably do not expect such large list is used- as it can be really tough to have your users selecting that many entries. Can you think of a way to improve usability (and hence avoid the problem), dividing the entries in subgroups or categories?
                • 5. Re: Limit for rows in a shuttle?
                  blue72TA
                  Kleber M wrote:
                  I believe that in your Apex version, the product's jquery code used to build the contents of the item P2_APPLY_TO_LINE is restricting it to the 32K limit, to prevent the error when running the PL/SQL code in your process or submitting the page. Developers probably do not expect such large list is used- as it can be really tough to have your users selecting that many entries. Can you think of a way to improve usability (and hence avoid the problem), dividing the entries in subgroups or categories?
                  Thanks for the feedback, I appreciate the sanity check. Our situation is we're using APEX in conjunction with Oracle Applications Quoting module. The APEX page (and a custom table) provide an extension to track data a lower level of detail for each quote line. It does occur that our Quotes have thousands of lines on them. We'll be doing what you suggest in that I will provide another field that can be used to filter the lines, or I'll provide a way for the user to update every line on the quote without using the shuttle when a 100% mass update is required.

                  Thanks again for your input!
                  • 6. Re: Limit for rows in a shuttle?
                    Patrick Wolf-Oracle
                    Hi,

                    you might want to have a look at http://deneskubicek.blogspot.com/2008/02/filtering-shuttle-item.html for an implementation of such a shuttle filter.

                    Regards
                    Patrick
                    • 7. Re: Limit for rows in a shuttle?
                      935660

                      I've run into what appears to be the exact same issue, except we are on Apex 4.0.2 and the limit is only 4000 characters! How do I get it up to 32k?