Stung by the criticism of our Football Web Pages APEX application, we decide to try to address the various issues raised by end users and peer code review.
- When clicking ‘Fixtures’, I get ‘ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request’
- If you enter a Competition only, it works fine. If you enter a Team only, it works fine. If you enter both a ‘Competition’ and ‘Team’, the results look weird. Should ‘Team’ be a cascading LOV based on the ‘Competition’ ?
- The column names and labels need tidying up. There are a lot of meaningless ID fields displayed.
- It would be nice to have the option to review past results separately from fixtures in the future.
- Performance - the Popup LOV’s for Competition and Team are sluggish. Why are they so S L O W ?
- The navigation menu looks chaotic and ugly.
HTTP 400 error entering Fixtures report
This error is because the REST parameter for 'Competition' or 'Teams' is required. When we first run this page, there no values defined for the 'Competition' or 'Teams' parameters which results in the error.
A quick and easy solution is to simply default the competition to 'Premier League'.
Edit the 'Fixtures' page. This is Page 8 in my example. The page number may be different in your APEX environment.
Edit the 'P8_COMPETITION' parameter.
Filter on 'Default' and configure a default value with 'Type' set to 'Static' with the value set to '1' (Premier League).
Save and test the changes. Now if you enter the 'Fixtures' page for the first time, you should see data displayed for the Premier League (instead of the HTTP 400 error).
This may not necessarily be the best solution to this problem. For example, I would like the 'Competition' to default to 'Isthmian League - South Central' or the team to default to 'Kingstonian FC'. This requirement could be implemented using a user profile page.
Parameter validation
Defaulting the 'Competition' to Premier League may appear to have resolved the issue when neither of the 'Competition' and 'Team' parameters are supplied. However, you can easily reproduce the original issue by selecting the 'Select' value for each parameter and clicking 'Go'.
APEX provides a declarative solution for parameter validation which we can use to resolve this issue.
Navigate to the 'Fixtures' page in App Builder.
Click the 'Processing' tab.
Create a Validation called 'ValidateParams'.
Click on 'Validating', then right-click and select 'Create Validation'. Scroll down to the 'Validation' section with Type = 'Expression' and Language = 'PL/SQL'.
I always forget the semantics of the validation expression so I normally click the 'Help' tab in the middle pane for a quick reminder.
To pass the validation, and not raise an error message, when the employee is in department 30 or is a manager:
( :P2_DEPTNO = 30 or :P2_JOB = 'MANAGER' )In our case, the two parameters are an exclusive OR. The user must either select a Competition or a Team but not both.
Therefore, our validation expression (which specifies when the parameters are valid) is:
(:P4_TEAM is null and :P4_COMPETITION is not null)
or
(:P4_TEAM is not null and :P4_COMPETITION is null)APEX insists you enter a meaningful error message to the displayed to the end user if the validation fails.
You must select a competition or team.
Test the changes and check the user now gets an error message displayed.
A lot of the FWP API's require this identical combination of parameters. Instead of copying and pasting this PL/SQL block, put it into a stored procedure, so you can reuse this logic. Then, if this logic subsequently changes in the future, you only have to change the code in one place.
Summary
We have (finally) addressed a couple of issues originally raised in October 2022.
This is the antithesis of the Agile development methodology but we will endeavour to address the remaining feedback during the course of 2025.