Mostly Making oracle easy, mostly:
Search for:
In ORDS

Building a REST API with ORDS, PL/SQL Functions, & Arrays

4 Mins Read

The Task

Build a REST API that return a JSON array list of strings, e.g. 

{
	"the_array": ["a", "b", "c", "d", "e"]
}

This list will come from a query. Each item is a row in said query.

How to do this?

Well dear reader, this is where I warn you that I’m not really a pl/sql developer. But, here’s what one could do with a bit of PL/SQL with ORDS.

Our Plan

  1. Build a PL/SQL type that is our array of strings.
  2. Write a plsql function that populates the array via BULK COLLECT, and RETURNs it.
  3. REST Enable the function, get a POST
  4. Take control, publish a REST API via a GET

Let’s have a quick look at my data.

I want a unique/distinct list of the BEER_TYPEs…my app needs it at least.

The SQL behind this is pretty darn simple.

select distinct beer_type from beers, more or less.

The thing is, I want a single row – a comma delimited list of strings.

If you think that sounds an awful lot like a PL/SQL array, that’s what I was thinking of as well. I mean, an array’s an array, yeah?

Let’s Create the Array

I’m going to create a first class TYPE object. This makes it available to other PL/SQL programs, and if I want to use the AUTO ORDS features on my PL/SQL function later, it needs to be able to ‘see’ what that array TYPE is, exactly.

create or replace type array_beers is varray(150) of varchar2(35);

Two things here, no bueno. 1, I hard-coded the data type (varchar(35), and 2, i hard-coded the size of the array. If you ever say the word ‘hard-coded’ out loud, that’s a big, red flag.

I should circle back on this, but for now, I want to get onto my Function.

Let’s Create the PL/SQL Function

I’m simply going to load the array with the SQL we’ve already seen, and RETURN it. That’s it. That looks something like this:

create or replace function get_beer_types return array_beers
is
   v_array array_beers;
begin
   select distinct beer_type
     bulk collect into v_array
     from beers
    order by 1;
   return v_array;
end;

Do I need to comment this code? Probably. Am I going to? No.

Let’s ORDS ‘Auto’ Enable my Function

Using the PL/SQL API for ORDS (I could also right-click on my function in either SQL Developer or SQL Developer Web) –

BEGIN
ORDS.ENABLE_OBJECT(p_enabled        => TRUE,
                   p_schema         => 'HR',
                   p_object         => 'GET_BEER_TYPES',
                   p_object_type    => 'FUNCTION',
                   p_object_alias   => 'get_beer_types',
                   p_auto_rest_auth => FALSE);
END;

Now let’s call it.

We use POST for Auto PL/SQL calls – ORDS doesn’t know if your PL/SQL is going to make changes or have side effects in your database or not…also, there’s a ‘/’ on the end of the URI, remember that or you’ll get a 404.

Success!

Or is it? Can I do better than 597 ms? And, what I don’t want the array to be named “~ret” ?

Let’s look at a module based REST API.

Manually Building the REST API

I get to pick my template and handler (a GET!), and the SQL is a simple SELECT of the function from dual;

ORDS will take that result set as shown in the test area, and pivot it to a JSON Array.

And if I call that in my browser (one of the nice things with HTTP GETs)…

Better? That’s up to you!

It ran in 45 ms vs 597 ms, that’s more than a 10x improvement. And my array is called what I want it to be, “the_beers.”

Why is the AUTO slower? Well, ORDS has to look up the PL/SQL function and the TYPE so it can do it’s magic, whereas the SQL based API simply just runs the SQL. If you used our CACHE feature, you could likely get this a bit closer (@krisrice saw something like 4x improvement) to the pure SQL/PLSQL based module.

Speaking of PL/SQL – We’re looking for a Product Manager

Want to rep, show off PL/SQL? Want to help build and develop a PL/SQL community of developers? Advocate or even help build out exciting PL/SQL based open source libraries? And show off what can be done with PL/SQL in ORDS and APEX? Well then, I may have a job for you.

I’m not a PL/SQL developer. I just help build tools that PL/SQL developers can use to do their jobs.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

8 Comments

  1. Yuri Reply

    Hi,
    Is there some kind of limitation that made you use VARRAY?

  3. Edisson Reply

    Hi Jeff,

    I am using ORDS V. 22.2.1.r2021302. I don’t know if it’s because I’m using apex_json.write().

    This is how I use sys_refcursor

    open l_cursor for
    select
    st.ticket_status_desc as “status”,
    s.ticket_json.title as “title”,
    cursor(
    select
    file_name as “file_name”
    from
    syw_ticket_file f
    where f.data_tickets_id = s.data_tickets_id
    ) as “files”
    from
    syw_data_tickets s
    inner join syw_ticket_status st on st.ticket_status_id = s.ticket_status_id
    where
    s.data_tickets_id = p_data_id;

    apex_json.open_object;
    apex_json.write(‘ticket_detail’, l_cursor);
    apex_json.close_object;

    • thatjeffsmith Post Author

      Those apex calls are necessary for ORDS. Have you tried using the native JSON features that ORDS provides for SQL/PLSQL responses?

      If you’re using APEX packages to generate the JSON, then you need to go ask the APEX folks for help.

  4. Edisson Reply

    Hi Jeff,

    I have a doubt regarding the objects in ORDS using SYS_REFCURSOR and it is that when it does not find values ​​it prints the key for me, example: “files”: null instead of files: [] and I did not find information on how I can return an empty object :(. It’s possible?

    regards.

    • thatjeffsmith Post Author

      Version of ORDS? In my example, an empty refcursor DOES come back the way you’re inquiring…

      {
      "album": []
      }

      album is the resultset parameter bound to the OUT of my PL/SQL program. You can find my example code here. See option #3.

Write A Comment