#1 PokeDex Project - API Consumption

Hey there!

Okay, so this time, let’s do things properly—and by “properly,” I mean actually starting from the beginning. That’s usually a great idea when you're building something, right?

So what’s the first thing we need when starting a project in Oracle APEX?

Tables.
And only after that—data.

“No environment setup? No schema creation? Nothing?”

Well… yeah! I mean, you’re here, and at this point, I really hope you’ve already signed up at apex.oracle.com your own workspace. It’s free, it’s easy, it works. (Just don’t use it for commercial stuff—that’s a bad idea. Trust me.)

So yeah, tables!

Let’s prepare our relational schema:

Wait… that’s it? Just 3 tables?

Yep. That’s all for now! We can always add more later. That’s the beauty of databases—you can always regret your decisions and fix them later. 😅

Here is the code:

🧱 Tables done. Now where’s the data?

Ok, we have the tables, where’s the data? That’s where the PokeAPI enter. Let’s cosume it 🍴.

Firstly let establish only thing, let’s do only the first generations, the OGs squad., ok? Ok.

🔁 The Theory: Easy Loop, Right?

In theory, it’s simple: 1. Hit the API; 2. Turn the JSON into a table; 3. Loop through it; 4. Do the inserts.

Easy, right?… Well, not quite, not for me at least.

The JSON is kinda... chunky. Also: it’s paginated. And that adds some spice.

“So the complication of using the api is the json complexity? But that’s how it usually goes”. Yes, apex is good to handle json when you wrap your head on how to do.

🧪 The Code

So let me show the code, and then we do some considerations:

Here’s the gist of what’s happening:

• Firstly, we need the URL, right? So start by saving the API URL in a variable.

• Then we call APEX_WEB_SERVICE.MAKE_REST_REQUEST with method GET.

• Inside a loop, we open the JSON and access the "results" tag—it’s a table with two columns: name and URL.

• We use REGEXP_SUBSTR() to extract the Pokémon ID from the URL (because of course the ID is hidden inside a string 🙃).

• Then we insert those values into our table.

That’s basically how the insert is done, but it doesn’t end like this. But wait, there’s more. 🧩

➕ The Pagination Thing

Each API call only gives us 20 results per page.
So we need to grab the "next" tag (yep, the next page URL), save it into our URL variable, and let the loop run again.

When we hit the end of the JSON or reach our limit (Gen 1!), we break the loop and call it a day.

🧼 Wrapping Up

So yeah, the code is a chunky boy, but once you break it down, it’s really not that scary. The logic is sound, the API is friendly (mostly).

That’s all for today—time to punch out and let the code rest. ☕
See you in the next post!