Building a Euro 2024 App with APEX and AI Assistance

With the release of Oracle APEX 24.1, the inclusion of AI-assisted development features has revolutionised how we approach application development. In this blog post, I'll share my experience building a basic Euro 2024 stats application using these cutting-edge tools. This application provides real-time statistics and insights for the Euro 2024 tournament, showcasing how AI can streamline the development process and enhance both functionality and user experience. Today, I will walk you through my steps (including all the times it didn't quite go to plan!) and give my opinions on the new AI assistance features.

My high-level approach:

• Find a suitable API to supply the data
• Create the REST Data source
• Synchronise the tables with the API
• Set up AI Credentials in APEX
• Create an application using the Generative AI functionality
• Make some AI-Assisted changes

Find a suitable API to supply the data

As the theme of this blog is the utilisation of AI, it would be rude not to use ChatGPT to find me a list of free Euro 2024 APIs I could use for my app. So, I did. I tested a few out and found one I liked from rapidapi.com. It had some useful basic info, which is all I was looking for.

Create the REST Data source

I need to set up my REST Data sources. This can only be done within an application, so I created a skeleton application purely for this purpose (ultimately, I want to use the generative AI feature to create the final app once the data and tables are in place, so I need to use this workaround for now).

Once the application is created, I add my REST data sources using the API endpoints and key. More information can be found here: Setting up a REST data source.

Synchronise the tables with the API

As this is not standing data, we need to get the latest data from the APIs at regular intervals. Data synchronisation was introduced in APEX v19.1 and allows us to allocate tables that are refreshed from API data at user-defined intervals. We can either select current tables to use or let APEX create them based on the data profile of the returned JSON. The latter is perfect for my needs in this case and is what I did.

More information on Data Synchronisation can be found here.

After an initial synchronisation, I have all my tables filled with the latest data. I set them to refresh every hour.

Set up AI Credentials in APEX

This is a pre-requisite for what comes next if you haven't done so already - you can find out how to link APEX to your AI provider here.

Create an application using the Generative AI functionality.

Now we have our tables and data sources in place we are ready to create our application using the new Generative AI creation feature. 

When you select this option, an APEX Assistant modal appears, asking me to describe the app I want to make. It can't be this easy, can it?

I describe a rough idea of my app, and it returns four pages with ideas on how to display the data for each.

This sounds ok, but I think an interactive report of players is boring, so let's ask to change it: 

Awesome! Let's go ahead and hit Create application to see what it comes up with.

I'm taken to the more familiar 'Create an Application' screen, and it has added the pages mentioned above. I hit create and wait for my app to cook.

So here are the pages it created:

Firstly, we have a Teams page complete with a card for each team, the coach's name and the country's badge:

This is a pretty good start. It's basic but is a good base to work from.

Next, we have a Players faceted search page:

It has included all the ID data, and there are some facets in there that I didn't ask for, but again, we have something to work on.

The top scorer chart doesn't look quite right. I wrote this at the last round of group stage matches, and I'm sure there are a couple of players on more than 1 goal, so we will need to tweak this. But at least we have a chart linked to the data. 

Finally, we have the top assisters bar chart:

It's fairly uninspiring, but I'm not sure what I expected.

It seems to have struggled with the charting setup, but it's not a bad attempt.

My Initial reaction to the Generative AI application creation is positive. It has saved me time and gave me a base to work with. For reporting on more standard datasets (as you see in most demos, 'Sample HR data come on down…'), you would probably get close to the desired outcome, however, for things like charts, it seems to struggle slightly to get what you require. Perhaps a more detailed prompt would have given me a better result.

Make some AI-assisted changes

So now we have our base pages, let's use the AI-assisted SQL Authoring and General Knowledge queries to make some enhancements. I want to add a logo to our app, as it looks a bit dull. I'm going to pretend I don't know how to do this and try out the APEX assistant. Currently, you can only access it via the code editor or SQL workshop (although I have an APEX idea to add a button accessible from everywhere here. If you agree, please give it a like).

Not quite correct, let's rephrase:

I mean, it's not far away, but the User interface is not found by going into Themes, and there's no such thing as the #WORKSPACE_IMAGES# button. If I were a new user, this hallucination would be pretty confusing.

I was hoping that the APEX documentation would have been fed into the AI model using RAG (Retrieval Augmented Generation), allowing users to query and receive accurate feedback. But, this doesn't appear to be the case, so hopefully, this will be in later releases.

Luckily, I know how to do this, therefore, I can add my logo with the link to the file in static files in User Interface. With a few tweaks to the theme, the app looks better already.

It looks like the team names and managers are all lowercase. Let's use the assistant to fix this:

I insert this code, validate and run and - 

Beautiful.

Let's make some changes to the player's faceted search screen. There are a lot of ID columns that we don't want to be visible to the end user:

If I open the APEX assistant from the query of the player's classic report, I can ask it to remove the ID columns: 

…or not. OK, let's try changing to the 'General Assistance' option and select .

Now, we are getting somewhere. I click insert and validate my new query and, we are good.

There are still a few things I would change in this report. Let's get productive and fix them all with one prompt:

Again, I insert, validate and run and…

Much better!

OK, in hindsight, a pie chart is a ridiculous way to display the top scorers. Let's delete this chart and create a simple classic report using the assistant.

Close, but not quite right. On validation, I'm getting an error:

 Let's try out the new Debug Assistant. I click – 'Help me fix this'.

This elaborates on the error and gives me guidance on how to sort.

All the data can be found in my EURO_SCORERS table. We don't need the suggested join.

I will try again and be more specific in my prompt:

It has got the columns correct but ignored my capitalisation request completely.

I will add these in manually and run.

I'll be honest, for such a simple query, I had to type lots of words and for the output to still not be correct. I am starting to question whether this will save me any time…

Let's stick with it, I want to change the assist chart to a classic report, but I want to add some aggregate data, hopefully, the assistant can help us out:

*sigh

Let's try 'General Assistance'.

Close, but for some reason, it has added team_name and minutes_played instead of the correct column names without the underscore. I can select 'Use Selection' in the code editor and ask it to refactor.

I give up at this point and manually amend the query.

It did understand my aggregation request, but the simpler tasks of changing column names failed miserably.

We got there in the end:

The assistant is contextual. If you are in a code editor, it is primed to assist with PL/SQL and SQL. Let's create a JS dynamic action and try getting some help to generate some JS in the editor:

It worked the first time as expected and gave me some insight into how it works, great.

Conclusion

I must be honest here and say that my experience with the AI Assistant was one of mostly frustration. Most times it didn't do quite as I asked, even with clear prompts. I found myself writing large prompts to try and change a couple of lines in a simple query.

I purposely overused the assistant to test it out and tried to come from the perspective of a new developer, and I feel it did the job OK, but it is not quite the complete 'no-code' experience just yet.

The app creation tool was really cool, and I will definitely be using this as it saved a lot of time creating the base pages and components I can tweak.

The AI-assisted debugging tool was nice. It would explain the error and, where possible, make the change for you at the click of a button which I liked.

Given my issues around the Query Builder, it would also be nice if it offered the context switch to General Assistance in cases where it can't help.

I currently use external AI tools to assist with more complex SQL functionality, CSS and JS assistance, so having the ability to do this from within the application will save me some time. I would like to see a button always visible on the builder. So we can access the assistant without having to be within a code editor for my last point to be pertinent.

Overall, the introduction of AI into APEX is exciting, and for a first iteration, this is an excellent attempt and will only improve.
Will it be enough for new, inexperienced users to build queries on simple datasets without much SQL experience? Most likely. For more experienced users, I still think it will be quicker and more accurate to write our own queries currently. However, I can see the balance shifting towards AI very shortly. 

24.1 really is a groundbreaking release. While the AI features (quite rightly) take the spotlight, there are a host of improvements. Check them out here.

For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.

Subscribe to Oracle APEX Insights if you want to stay tuned for more APEX updates.