Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to customize APEX error page for "Page isn't available"

jackwApr 14 2022

Is is possible to change the verbiage on the standard "Sorry, this page isn't available" error page in APEX? This is the one you get when a user enters a url to a page that does not exist in an application, like this:
APEX_Page_Not_Available.pngTo reproduce this, for example, if this is our valid APEX application url:
https://apps.mycompany.com/ords/myworkspace/r/tsat/home
And a user enters a non-existent page name (likely one that previously existed but where we have since removed, and therefore breaking the user's saved bookmark), something like this:
https://apps.mycompany.com/ords/myworkspace/r/tsat/blah <- There is no "blah" page in the app
Then the user will get "Sorry, this page isn't available" error page above. Is it possible to customize the text on that error page?
This post has been answered by InoL on Apr 14 2022
Jump to Answer

Comments

Eight Six
Try this not tested


select y.date, case when x.dt1 is null then max_name ELSE x.name END
from
(
select case when lag(date) over( order by date1) is null then to_date('01/01/0001','mm/dd/yyyy') ELSE END as dt1, date1 as dt2 , name ,
max(name) keep (dense_rank first order by date1 desc) as max_name
from table1
)x
Right Outer Join
table2 y
on (y.date1 >= x.dt1 and y.date1<=x.dt2)



Thanks
Aketi Jyuuzou
I think this is a very nice question :-)
It is very important to mension Oracle version at OP.

I like ScalarSubQuery B-) 
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b) as NAME
from table2 a;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Boolean algebra in some Rows tutorial by Aketi Jyuuzou
1007678
Etbin
Aketi Jyuuzou wrote:
I think this is a very nice question :-)
and yours is for certain a remarkable answer ... hats off

Regards

Etbin
elmasduro
Aketi, this is great query thanks. i have one more questions. I changed my data in such a way that i introduce a new field call IND.
so my data now looks like this 
WITH table1 AS(
SELECT 111 ind, To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 111 ind,To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual UNION all
SELECT 112 ind, To_Date('3/12/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 112 ind,To_Date('3/18/2010','mm/dd/yyyy') date1, 'smith' name FROM dual
),
table2 AS(
SELECT 111 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual union ALL

SELECT 112 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 112 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual 

)
as you can see, each id in table1 has conrresponsdent dates in table2. so each id in table1 should match to
its correspondent id in table2. so the output for the data above should be
IND  DATE1     NAME
---- --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith

112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match to the ind in table2.
will the current query take care of this scenario.
thanks

Edited by: elmasduro on May 1, 2010 12:09 PM
Aketi Jyuuzou
This morning of Japan.
I realized this using row_number solution :-)
This solution avoid cross join of my previous solution B-)
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,name
from (select a.date1,b.name,
      Row_Number() over(partition by a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,
              max(date1) over() as maxData1
                from table1) b
          on a.date1 <= b.date1 or b.date1=b.maxData1)
where rn=1;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Aketi Jyuuzou
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match >to the ind in table2.
will the current query take care of this scenario.
In that case, we have to use inner Join and partition by of OLAP :-)

create table table1(ind,date1,name) as
SELECT 111,To_Date('3/13/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 111,To_Date('3/17/2010','mm/dd/yyyy'),'smith' name FROM dual UNION all
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 112,To_Date('3/18/2010','mm/dd/yyyy'),'smith' name FROM dual;

create table table2(ind,date1) as
SELECT 111,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/20/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/25/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('4/20/2010','mm/dd/yyyy') FROM dual union ALL
SELECT 112,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 112,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('4/20/2010','mm/dd/yyyy') FROM dual;
select IND,date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b
  where b.ind = a.ind) as NAME
from table2 a
order by IND,DATE1;

IND  DATE1     NAME
---  --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
Aketi Jyuuzou
of course we can use below solution,too
select ind,date1,name
from (select a.ind,a.date1,b.name,
      Row_Number() over(partition by a.ind,a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,ind,
              max(date1) over(partition by ind) as maxData1
                from table1) b
          on a.ind = b.ind
         and (a.date1 <= b.date1 or b.date1=b.maxData1))
where rn=1
order by IND,DATE1;

IND  DATE1     NAME 
---  --------  -----
111  10-01-16  john 
111  10-03-12  john 
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john 
112  10-03-12  john 
112  10-03-16  smith
112  10-04-20  smith
elmasduro
Aketi, thanks a lot for your help. i really appreciate it. keep up the good work. the last query you send me is exactly what i am looking for. thanks again
1 - 8

Post Details

Added on Apr 14 2022
#apex, #apex-discussions
3 comments
1,094 views