ORDS - Restituzione di un tracciato record json con array_json

Di seguito un esempio di come creare una procedura pl-sql da richiamare in ORDS che generi un particolare tracciato record json. In questo caso il tracciato record è il seguente:

{
   "count": "1",
   "operation": [   {
      "operation_id": "202003020301301",
      ...
      "end_date": null
   }],
   "flows_requested": [   {
      "rownum": 1,
      ...
      "end_date": null,
      "missing_items": [      {
         "column_name": "COL_01",
         "column_value": "12"
      }]
   }]
}

Il seguente json è composto da tre pezzi separati:

1. Il primo record rappresenta una count
2. Il secondo pezzo rappresenta un array contenente l'estrazione di una serie di informazioni presenti in una determinata tabella.
3.  Nel terso pezzo abbiamo un cursore innestato con un altro cursore.

 
Per ottenere una strutura di questo tipo occorre creare una procedura pl-sql contenente tre cursori, di seguito l'esempio del pl-sql che genera il tracciato json sopra indicato.

PROCEDURE   get (  
               [parametri di input...]           
                      OUT_OPERATION        OUT SYS_REFCURSOR,
                      OUT_COUNT            OUT NUMBER,                     
                      OUT_FLOWS_REQUESTED  OUT SYS_REFCURSOR
                      )

AS
[dichiarazioni di variabili...]

BEGIN
------------------------------
--- count
------------------------------

SELECT
      COUNT(1) INTO OUT_COUNT
FROM( [tabella o sottoquery su cui effettuare la count...] );






[gestione dello stato nel caso ritorni 0 record...]

------------------------------

--- sys_refcursor
------------------------------
OPEN  OUT_OPERATION  FOR
SELECT DISTINCT
       req.OPERATION_ID operation_id
     [...]
     , req.odi_end_date
  FROM [...] ;
------------------------------

--- sys_refcursor
------------------------------
OPEN OUT_FLOWS_REQUESTED FOR
SELECT rownum,
       flow_id,
       [...],  
       end_date,  
       cursor (select miss.column_name, miss.column_value
               from [...]
                where [...])   missing_items     
  FROM [...]
 WHERE [...];


END get;

A questo punto occorre creare l'Handler di ORDS che richiama questa procedura pl-sql ed otterremo il tracciato indicato sopra.