Oracle Rest Data Service return a collection as JSON

Question

I have some data in a collection, and I would like to send it as a result set in the response body.

Is it possible parse an associative array to JSON, and then send it as a result set?

Answer 1

@RobertoHernandez gave a basic overview of the ORDS service which is not exactly what I was searching for, but thanks!

1. The function which fills the collection and creates the JSON object:

CREATE OR REPLACE FUNCTION f_get_data RETURN CLOB
AS
    cst_key CONSTANT VARCHAR(32) := 'KEY';
    cst_value CONSTANT VARCHAR(32) := 'VALUE';

    TYPE r_summary IS RECORD (
        key   VARCHAR2(255),
        value NUMBER
    ); 
    
    TYPE t_summary IS TABLE OF r_summary INDEX BY BINARY_INTEGER;
    
    r_s     r_summary;
    tab_s t_summary;

    json_clob CLOB;
 
    i INTEGER;   
BEGIN
    -- insert some dummy data to the collection
    FOR i IN 1..10 LOOP
        r_s.key := dbms_random.string('A', 10);
        r_s.value := ROUND(dbms_random.value(1,10), 2);
        
        tab_s(tab_s.COUNT + 1) := r_s;
    END LOOP;
    
    -- write data to a JSON object
    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
    
    APEX_JSON.open_array('data');
   
    IF tab_s.COUNT > 0 THEN
        FOR i IN tab_s.FIRST..tab_s.LAST LOOP
            IF tab_s.EXISTS(i) THEN
                APEX_JSON.open_object;
                APEX_JSON.write(cst_key, tab_s(i).key);
                APEX_JSON.write(cst_value, tab_s(i).value);
                APEX_JSON.close_object;
            END IF;
        END LOOP;
    END IF;
    
    APEX_JSON.close_all;
    
    json_clob := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;
   
    RETURN json_clob;
END;

2. Create the web service as:

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'test',
      p_base_path      => '/data/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);  
  
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test',
      p_pattern        => 'all',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'all',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT
    ''application/json'',
    f_get_data
FROM
    sys.dual'
      );


  COMMIT; 
END;

3. Finally call the ORDS:

http://<host>:<port>/ords/<db>/<schema>/data/all/

For more details read the following article which gave me the idea (credis to @thatjeffsmith ):

https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/