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
– Naq_23, Commented Jun 8, 2018 at 19:55
It is probably because of the SQL . When I try it with a simple table it returns values quickly and also loads — Naq_23
– Naq_23, Commented Jun 8, 2018 at 20:28

Littlefoot · Accepted Answer · 2018-06-08 20:33:24Z

1

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.

answered Jun 8, 2018 at 20:33

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Naq_23 Over a year ago

I don't want it to display 7 million rows . I wanted it to display one out of the 7 million . Your explanation makes sense . I don't understand how it can run the query when its not given with the value for the variable .

Littlefoot Over a year ago

The first CASE condition will evaluate to 1 = 2 (i.e. false) when P2_NEW is null and query won't return anything. Once you put something into the item, it'll become 1 = 1 (i.e. true) and query might return the result (if fullorderno like SUBSTR(:P2_new,1,8) || '%' condition is met).

Naq_23 Over a year ago

The "Always, replacing any existing value in session state" helped I think . I also changed the like to '=' .

Littlefoot Over a year ago

Changing LIKE to = can't help, unless FULLORDERNO ends with a percentage sign. Besides, it is usual to "reward" someone who helped either by accepting their answer or upvoting it (instead of accepting your own (apparently, wrong) solution).

Naq_23 Over a year ago

Calm down . I did up vote your answer . I just don't have a high enough reputation for my up votes to show . I accepted my answer because I thought that was the right solution with your supporting explanation and I deleted it when it didn't work .

|

Collectives™ on Stack Overflow

Oracle Apex Web Development

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related