This discussion is archived
4 Replies Latest reply: Jan 25, 2013 3:52 AM by William Wallace RSS

Weird APEX error when refreshing report

William Wallace Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points