Does ORDS Support Query/Collection and Attribute Arrays?

Why do I blog? For one of the following reasons:

• I need to write stuff down so I don’t forget it
• to answer a question
• to share the good news with the people

In this case, I’m hitting all 3 checkboxes.

I got this question this morning –

I want to rewind things a bit.

What is ORDS, and why is it awesome?

ORDS has a few very powerful features. ORDS:

• makes REST APIs for Oracle trivial to implement
• marries SQL/PLSQL with HTTPS
• does so much work FOR YOU

I think this question is a very good example of these things.

For example, let’s say I’m using a USER defined type (UDT) to represent that “parent_ids” array in the {json} response as shown in the example.

That would mean I could simply use a SELECT query to back the GET handler for my REST API, of type ‘Collection Query.’

What does Collection Query get me? Well it gets me a few things, but the primary one is paging, which is very nice. Because my SELECT * FROM query which returns 10,000 rows, is suddenly, magically paged into groups of 25.

The Oracle Database is also very powerful and flexible. One piece of this is the maturity of the PL/SQL language. I can define a TYPE, which is an ARRAY of NUMBERS, and I can use this TYPE to construct a TABLE.

See where I’m going with this?

PS I’m reading the question again, and they wanted an array of STRINGS and not NUMBERS, but that’s a trivial change to the TYPE definition. The answer remains at heart, the same.

The Table/Type/Data

I’ll create a TYPE, a TABLE, and INSERT a row. In my scenario I’ve populated the table with 3 rows…

Quick Shout-Out to LiveSQL – this scenario was easy to create with the existing code samples we share with customers so they can learn SQL and PL/SQL. Check it out!

CREATE OR REPLACE TYPE list_of_numbers_t IS  
   VARRAY(10) OF NUMBER(7,0); 
/  
 
CREATE TABLE swaters (project_name VARCHAR2(30), parent_ids list_of_numbers_t);
 
 
DECLARE 
 lists list_of_numbers_t := list_of_numbers_t ();
BEGIN
 lists.EXTEND(3);
 lists (1) := 300;
 lists (2) := 400;
 lists (3) := 500;
 
 INSERT INTO swaters VALUES ('some other other  project', lists);
 COMMIT;
END;
/

The REST API

Here’s a screenshot of it, just working.

And I’m happy to share the REST API definition as well –

 
-- Generated by ORDS REST Data Services 21.3.0.b2421319
-- Schema: HR  Date: Tue Sep 21 10:47:24 2021 
--
 
BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'swaters',
      p_base_path      => '/swaters/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'swaters',
      p_pattern        => 'lov/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'swaters',
      p_pattern        => 'lov/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from swaters');
 
COMMIT;
 
END;