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 –

Short answer: YES!

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);
 lists list_of_numbers_t := list_of_numbers_t ();
 lists (1) := 300;
 lists (2) := 400;
 lists (3) := 500;
 INSERT INTO swaters VALUES ('some other other  project', lists);


Here’s a screenshot of it, just working.

The only real code here for my REST API is ‘select * from swaters’ – that’s how simple this is.

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 
      p_module_name    => 'swaters',
      p_base_path      => '/swaters/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
      p_module_name    => 'swaters',
      p_pattern        => 'lov/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
      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');


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

Write A Comment