I am trying to build an Oracle Apex web page. I have the SQL query which I run in Db Visualizer and is fine. It's supposed to fetch about 7 million records but stops at 1000 because of limiting the rows in DB Vis.
This is my SQL query:
select t.*, t.rowid from table t
where custedp =
( select min(custedp) from tablex
where fullorderno like substr('${ORD}$',1,8) || '%' );
I am trying to enter the ORD variable through a page item which is P2_new so my new SQL query is
select t.*, t.rowid from table t
where custedp =
( select min(custedp) from tablex
where fullorderno like substr(:P2_new,1,8) || '%' );
I created a region in the web page new (static content),page item (text field) p2_new and button submit which is going to submit the page. Everything until here is fine when I change the region new to a classic report from a static content the page doesn't load at all . It has a loading... on the tab and the address bar shows "about:blank" . I tried to validate the code and it also showed me an
ajax call returned server error ora-20001 :error at line 1 error when I
tried to validate the code using the in built validator .
What do you plan to do with 7 million rows presented on a single page?
Anyway: classic report's query runs as soon as you navigate to that page. Thanks to || '%', query searches for min(custedp) throughout the whole tablex, without waiting for you to enter something into the P2_NEW item and press the SUBMIT button. So, if you wait long enough, it might return something.
Or, modify that query so that it runs if P2_NEW contains some value. For example:
select t.*, t.rowid
from table t
where custedp = (select min(custedp)
from tablex
where 1 = case when :P2_NEW is null then 2
else 1
end
and fullorderno like SUBSTR(:P2_new,1,8) || '%'
);
Also, consider setting P2_NEW's source Always, replacing any existing value in session state.