ORDS output variable inside a JSON Object

In ORDS, I was working with a GET PL/SQL Handler and I need to produce an output variable inside a JSON Object. My ORDS version, as of today, is 22.1.6.

Example:

BEGIN
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE
  L_CUR        SYS_REFCURSOR;
BEGIN

  OPEN L_CUR FOR     SELECT LEVEL just_a_column
                     FROM dual
                     CONNECT BY LEVEL <= 2;

  :name_out  := ''Name'';
  :name_desc := ''Description'';

  :ret := L_CUR;
END;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'items',
      p_bind_variable_name => 'ret',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'metadata.desc',
      p_bind_variable_name => 'name_desc',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'metadata.name',
      p_bind_variable_name => 'name_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);
END;
/

The current output was:

{
  "items": [
    {
      "just_a_column": 1
    },
    {
      "just_a_column": 2
    }
  ],
  "metadata.desc": "Description",
  "metadata.name": "Name"
}

But my desired output was:

{
  "items": [
    {
      "just_a_column": 1
    },
    {
      "just_a_column": 2
    }
  ],
  "metadata": {
    "desc": "Description",
    "name": "Name"
  }
}

So, unfortunately, ORDS still can’t handle . in the parameter name as a sub-attribute.

I tried to create another result set, named it metadata, and got a bit closer to my desired output:

New PL/SQL query:

DECLARE
  L_CUR        SYS_REFCURSOR;
  L_CUR_2      SYS_REFCURSOR;
BEGIN

  OPEN L_CUR FOR     SELECT LEVEL just_a_column
                     FROM dual
                     CONNECT BY LEVEL <= 2;

  OPEN L_CUR_2 FOR   SELECT 'Name' "name", 'Description' "desc"
                     FROM dual;


  :ret := L_CUR;
  :ret2 := L_CUR_2;
END;

New output now:

{
  "items": [
    {
      "just_a_column": 1
    },
    {
      "just_a_column": 2
    }
  ],
  "metadata": [
    {
      "name": "Name",
      "desc": "Description"
    }
  ]
}

Almost. If we had an output para type as RESULTSET ONE ENTRY the problem would be solved.

Also, if in ORDS I had a variable output type as JSON, I could also manually create my own JSON to spool. The current valid values are: STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP.

So, in the end, to solve my issue, I changed my strategy, and instead of returning the REF_CURSOR, I had to manually create the JSON and append my desired tag:

DECLARE
  L_CUR SYS_REFCURSOR;

BEGIN
  OPEN L_CUR FOR SELECT LEVEL JUST_A_COLUMN
                   FROM DUAL CONNECT BY
                   LEVEL <= 2;

  OWA_UTIL.MIME_HEADER('application/json');
  APEX_JSON.INITIALIZE_CLOB_OUTPUT;
    
  APEX_JSON.OPEN_OBJECT; -- {

  APEX_JSON.WRITE('items', L_CUR); -- items

  APEX_JSON.OPEN_OBJECT('metadata'); -- metadata
  APEX_JSON.WRITE('name', 'Name');
  APEX_JSON.WRITE('desc', 'Desc');
  APEX_JSON.CLOSE_OBJECT; -- }

  APEX_JSON.CLOSE_OBJECT;

  HTP.PRN(APEX_JSON.GET_CLOB_OUTPUT);
END;

New output:

{
  "items": [
    {
      "JUST_A_COLUMN": 1
    },
    {
      "JUST_A_COLUMN": 2
    }
  ],
  "metadata": {
    "name": "Name",
    "desc": "Desc"
  }
}

Victory.

PS: If you want to check a more complete solution, check this post: https://www.dbarj.com.br/en/2022/11/ords-pagination-with-pl-sql-source-type/

Hopefully, in the next releases, ORDS will create one of the following approaches in order to solve this requirement in an easier way: