This content has been marked as final. Show 4 replies
William Wallace wrote:It doesn't work like that. In
I am trying to refresh a report with dynamic action. And getting the following errors:
ORA-20876: Stop APEX Engine,
I have identified the problematic part. I have a multi-select select list and my query goes like this:
Previously this line:
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
used to be just
when :P22_SALESREP IS NOT NULL and m.salesrep_id in (:P22_SALESREP) then 1
and it would work. But have requirement to make this select list multi-select.
when :P22_SALESREP IS NOT NULL and m.salesrep_id i=:P22_SALESREP then 1
<tt>(7788, 7839, 7876)</tt> is an expression list and the predicate is evaluated as a membership condition.
select * from table where columnvalue in (7788, 7839, 7876)
<tt>:P1_X</tt> is a scalar string, incapable of containing multiple values.
select * from table where columnvalue in (:P1_X)
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":
where <tt>P1_X</tt> has been sanitized for SQL injection and string values are properly quoted if necessary.
return 'select * from emp where empno in (' || :P1_X || ')';
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.
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.
Edited by: klsharpe on Jan 24, 2013 1:50 PM
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:
So my code would look like:
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;
Now it all works. Thanks for help.
when :P22_SALESREP IS NOT NULL and item_in_list(m.salesrep_id,:P22_SALESREP) = 1 then 1