Oracle Apex Web Development

Question

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 .

the page is not loading for me to enter any value . The page in theory looks perfect but it's not loading at all . The SQL is fine . — Naq_23
It is probably because of the SQL . When I try it with a simple table it returns values quickly and also loads — Naq_23

Littlefoot Littlefoot · Accepted Answer · 2018-06-08T20:33:24

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.

Oracle Apex Web Development

1 Answers