Can AI generate Oracle Database REST APIs? TL;DR Kinda!?

The views expressed in this post are my own and do not necessarily reflect the views of Oracle.

Last week, I published an example of a ‘paged’ REST API involving a PL/SQL stored procedure and a REFCURSOR. And like some sort of sucker, I spent a lot of time writing that code, all by myself.

But hey, it’s 2023, what about AI??? Today’s post is a look at what happened when I prompted AI to do the work for me, in like 3 seconds, vs the 3+ hours.

For a SQL based REST API, paging is easy, so easy in fact, we (Oracle via ORDS) do it for you, no code! But for a PL/SQL program returning a REFCURSOR, you need to code in the paging, yourself.

So let’s see.

Disclaimer Mind you, I’m not a ‘master prompter.’ and I did try 5-6 different variations and follow-ups, and never got something truly ‘complete.’ but I do see a ton of promise, and immediate practical value.

Code?? Who writes code anymore? I have a magic box!

So, can our favorite ‘magic box’ generate the code we want, the code we need, from scratch?

Let’s find out!

I need an Oracle Database REST API for a PLSQL refcursor with paging logic

– lazy dev/product manager

I’ll even share with you one of the scenarios I worked thru.

Let’s try it out.

I mean, hey I’m scrolling thru the answer, and it seems a bit promising!

So this could be good, but is it?

I won’t waste too much more of your time, if you’ve made this this far, here’s what you need to know.

It can help you with certain bits, and I even used it to make sure my ‘previous page’ logic/maths checked out, but it’s just not ‘there’ yet.

The Good

It’s actually generating code that COMPILES without errors. I only had to substitute the dummy table and column names.

But does this program actually, ‘work?’

This might not be ideal.

Doing a row count on the query really is the only way to know the total number of rows. This means we’re effectively running the query 2x. So YOU, the developer, need to decide if that cost is worth it or not. That effectively means you’ll either know the total number of pages, or you won’t.

If your query is sub-millisecond, maybe no-harm, no-foul. But, if you’re query is a half a second, that might mean it’s not suitable for your REST API application.

Moving onto our AI Generated REST Module

This is what I’m basically after, help with these bits, but I did find it quite polite to include PL/SQL we just ‘reviewed.’

Now the tricksy parts, our template and handler.

Before I pick this apart, let’s just copy and paste the code into our real environment, and see what happens.

So we now have a REST API, let’s go look at it.

So using the REST Workshop in my database, I’ll pull up the module, and jump down to the Handler code/declarations.

The curious case of our magic :bind variables

This line of code –

get_data_paged(:page_number, :page_size, :result, :total_rows);

For it to work, we’ll need those variables to either be declared, or derived. The inputs, :page_number and :page_size, could be automatically derived from the POST request JSON payload…but we’re doing a GET.

We could derive them from the URI as parameters, but then we would need to declare them. Like so –

The two outputs, :result and :total_rows, well, we have to tell ORDS what to do with the REFCURSOR and INTEGER being returned from the stored procedure.

Most likely, you’d want them used to produce the JSON payload on the RESPONSE, which I demonstrate here –

Not bad, just incomplete

So the AI hasn’t really put me in a bad place here. I just need to augment what it’s done to complete the job. And that’s super helpful for anyone, include a developer (hack) like myself.

With a little more training time, it would only take the AI a few more ORDS PLSQL calls to handle those bind variables and the resulting response payload (output).

Stopping whilst you’re ahead

If the AI had actually stopped here, it would have been OK. But it didn’t, there was more.

There is no such thing as ‘enabling SQL query for the module.’

And worse, running that DEFINE_HANDLER for a SELECT query on the exact same module and template, will result in the previous one (incomplete, but not horrible) being destroyed.

Now, if you do run that, you will get a paged result like the AI is showing in the results below, but that defeats our purpose of using PL/SQL and NOT SQL.

Let’s end with another quick win here

Momma said not to say something about someone unless it was nice, and I don’t want to turn this post into a ‘dunking on the AI’ bots, because it’s not that.

My brain was mush earlier this week, trying to get my head straight on calculating what the offset would be for a previous page.

The AI got it ‘right.’ or at least right enough for me to finish my post. So thanks AI!

prev_link := greatest(param1 - param2, 0); --AI was more sober than me
...
and then my code to omit the previous page link unless necessary
if param1 <> 0 THEN
  :prev   := '../paged?skip=' || prev_link || '&read=' || next_link_param2;
end if;

You can read that example in full here. Note that I didn’t include the TOTAL NUMBER of rows metric, because I didn’t want to pay the ‘run the query twice’ tax.

P.S. Another take on paging/getting the number of rows

On another prompt session, Kris shared with me a scenario generated that used, or tried to use an analytic function to ‘window’ the results, giving us paging AND the total number of rows, all in one go.

It got pretty close.

Again, not bad. But you really need to inspect, test, and verify ANY code that you’re given, whether it’s from a human or a human talking to a prompt/bot.

And the Cursor results always include the rows numbers and total number of rows, which I didn’t want to include. Your app using said REST API can easily ignore unwanted attributes, so maybe I’m just being silly.