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:
- Interpret
.
in the parameter name as a sub-attribute. - Have a variable output para type as
RESULTSET ONE ENTRY
. - Have a variable output type as
JSON
.
Meanwhile, you can use the approach above to create your JSON object entry.
Have you enjoyed? Please leave a comment or give a 👍!