4 Replies Latest reply: Jan 25, 2013 5:52 AM by William Wallace RSS

    Weird APEX error when refreshing report

    William Wallace
      Hi All,

      I am trying to refresh a report with dynamic action. And getting the following errors:

      {"dialog":{"uv":true,"row":[{"V":"Widget Failure
      ORA-20876: Stop APEX Engine,
      classic_report"}]}}

      I have identified the problematic part. I have a multi-select select list and my query goes like this:
      select o.order_id, s.salesrep_name
      from ml_orders o, ml_order_salesrep_mapping m
      where m.order_id(+) = o.order_Id
      and case when :P22_SALESREP is null then 1
      when :P22_SALESREP IS NOT NULL and m.salesrep_id in (:P22_SALESREP) then 1
      else 0
      end = 1
      Previously this line:
      when :P22_SALESREP IS NOT NULL and m.salesrep_id in (:P22_SALESREP) then 1 
      used to be just
      when :P22_SALESREP IS NOT NULL and m.salesrep_id i=:P22_SALESREP then 1
      and it would work. But have requirement to make this select list multi-select.

      Any help appreciated.

      Using Apex 4.2, classic report with refresh by dynamic action upon button click.
        • 1. Re: Weird APEX error when refreshing report
          fac586
          William Wallace wrote:

          I am trying to refresh a report with dynamic action. And getting the following errors:

          {"dialog":{"uv":true,"row":[{"V":"Widget Failure
          ORA-20876: Stop APEX Engine,
          classic_report"}]}}

          I have identified the problematic part. I have a multi-select select list and my query goes like this:
          select o.order_id, s.salesrep_name
          from ml_orders o, ml_order_salesrep_mapping m
          where m.order_id(+) = o.order_Id
          and case when :P22_SALESREP is null then 1
          when :P22_SALESREP IS NOT NULL and m.salesrep_id in (:P22_SALESREP) then 1
          else 0
          end = 1
          Previously this line:
          when :P22_SALESREP IS NOT NULL and m.salesrep_id in (:P22_SALESREP) then 1 
          used to be just
          when :P22_SALESREP IS NOT NULL and m.salesrep_id i=:P22_SALESREP then 1
          and it would work. But have requirement to make this select list multi-select.
          It doesn't work like that. In
          select * from table where columnvalue in (7788, 7839, 7876)
          <tt>(7788, 7839, 7876)</tt> is an expression list and the predicate is evaluated as a membership condition.

          In
          select * from table where columnvalue in (:P1_X)
          <tt>:P1_X</tt> is a scalar string, incapable of containing multiple values.

          See +varying elements in IN list+ on Ask Tom, and +emulating string-to-table functionality using sql+ for potential solutions.

          In an APEX standard report, use a PL/SQL function body returning an SQL query report source with lexical substitution to produce a "varying IN-list":
          return 'select * from emp where empno in (' || :P1_X || ')';
          where <tt>P1_X</tt> has been sanitized for SQL injection and string values are properly quoted if necessary.
          • 2. Re: Weird APEX error when refreshing report
            klsharpe
            William,
            I am also getting the error displayed after a classic report refreshed by a dynamic action. My query is simple. No list items, two criteria columns provided with values from text-entry page items.

            Kelly
            • 3. Re: Weird APEX error when refreshing report
              klsharpe
              I just tried to duplicate this error text on apex.oracle.com and was not getting it using Theme 24.

              I did get the error text to appear when the region is in a Reports Region and the report template is Standard - Alternative. BUT I also added a DIV in Report Attributes Substitution to force a scroll bar (div style=width:475px; height:200px; overflow:auto; - took out double quotes). (I added this div to my app on my work environment.)

              I added #REPORT_ATTRIBUTES# to the Alternative template - before any other text in the Before Rows field.

              The substitution already exists in the Standard report template, inside of a uReportContainer class, but does not get that error text.

              So I suggest you try other report templates and see if the error disappears.
              Thanks,
              Kelly

              Edited by: klsharpe on Jan 24, 2013 1:50 PM
              • 4. Re: Weird APEX error when refreshing report
                William Wallace
                Thanks for suggestion. I don't think its anything to do with theme of application.


                I have created a function to check in a list which is:
                create or replace FUNCTION item_in_list(x_item IN NUMBER, x_list in VARCHAR2)
                RETURN NUMBER
                IS
                    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
                    
                    TYPE number_list is TABLE OF NUMBER;
                    num_list number_list := number_list();
                    
                    x_ret NUMBER;
                BEGIN
                    /* checks if x_item exists in the comma delimited list x_list */
                    
                        l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(x_list);
                        
                        FOR i IN 1..l_vc_arr2.COUNT LOOP
                            num_list.EXTEND;
                            num_list(i):= CAST(l_vc_arr2(i) AS NUMBER);
                        END LOOP;
                        
                        IF x_item MEMBER OF num_list then 
                            return 1;
                        else
                            return 0;
                        end if;
                
                
                END;
                So my code would look like:
                when :P22_SALESREP IS NOT NULL and item_in_list(m.salesrep_id,:P22_SALESREP) = 1 then 1 
                Now it all works. Thanks for help.