ORDS - Impaginazione dei risultati di output di un REST

Di seguito un esempio di come introdurre la paginazione all'interno di un REST, questo può essere utile se il numero di record è elevato e si vuole restituire un risultato il prima possibile in modo da fare vedere prima i primi 1000 record e così via.

Supponiamo di richiamare un pl-sql nel nostro handler:

Vediamo prima la struttura del pl-sql e poi dell'handler. Di seguito la struttura della procedura pl-sql richiamata in un package.


PROCEDURE getElement (  
                          user_id                   in  varchar2,
                          element_type_cd           in  varchar2     default null,

---- Parametri impaginazione                            
                            page        in number default 1,
                            numrows     in number default 1000,
---- Fine Parametri impaginazione

                           
                       OUT_COUNT         OUT NUMBER,            
                       OUT_ELEMENT_TYPES OUT  SYS_REFCURSOR)
AS

--- <dichiarazioni di variabili >
...
---------------------------------------------
---- calcolo paginazione
---------------------------------------------
l_page number := (page * numrows) - numrows;
l_rows number := numrows;

BEGIN


------------------------------
--- count
--------------------------------------------------------
SELECT  COUNT(1) INTO out_count
   FROM    ....

IF OUT_COUNT = 0 THEN
  RAISE NO_DATA_FOUND;
END IF;

------------------------------
--- sys_refcursor
------------------------------

  OPEN OUT_ELEMENT_TYPES FOR
WITH
  js AS (SELECT ...     )
            )
  SELECT  js.*, jt.*
FROM js , jt
offset l_page rows fetch next l_rows rows only;


END getElement;

A questo punto vediamo la definizione della get:

BEGIN
 
GETELEMENT(
    USER_ID => :USER_ID,
    ELEMENT_TYPE_CD => :ELEMENT_TYPE_CD,
    PAGE => nvl(:pag,1),
    NUMROWS => nvl(:numrows,1000),
    OUT_COUNT => :OUT_COUNT,
    OUT_ELEMENT_TYPES => :OUT_ELEMENT_TYPES
  );


END;

 A questo punto occorre definire le variabili di input ed output del REST.

A questo punto basta effettuare una chiamata REST con SOAP_UI o POSTMAN fornendo in input le varibile difinite dal package, se PAGE o NUMROWS non saranno valorizzate in automatico passiamo 1 e 1000, per andare avanti nella paginazione effettuare una seconda chiamata e passare un nuovo numero di pagina o di righe da ritornare.

Finito.

RDBMS ORACLE - SPLIT TEXT with Function

Altro modo per ottenere lo split di una stringa è l'utilizzo di una funzione creata appositamente per ritornare i singoli componenti della stringa divisi da un delimitatore:


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

FUNCTION split_text (p_text       IN  CLOB,
                     p_delimeter  IN  VARCHAR2 DEFAULT '§')
  RETURN t_split_array IS
-- ----------------------------------------------------------------------------
  l_array  t_split_array   := t_split_array();
  l_text   CLOB := p_text;
  l_idx    NUMBER;
BEGIN
  l_array.delete;

  IF l_text IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'P_TEXT parameter cannot be NULL');
  END IF;

  WHILE l_text IS NOT NULL LOOP
    l_idx := INSTR(l_text, p_delimeter);
    l_array.extend;
    IF l_idx > 0 THEN
      l_array(l_array.last) := SUBSTR(l_text, 1, l_idx - 1);
      l_text := SUBSTR(l_text, l_idx + 1);
    ELSE
      l_array(l_array.last) := l_text;
      l_text := NULL;
    END IF;
  END LOOP;
  RETURN l_array;
END split_text;
-- ----------------------------------------------------------------------------
 Questa funzione può essere richiamata all'interno di uno statement pl-sql nel seguente modo:

PROCEDURA PL-SQL
....

str_1 t_split_array;

BEGIN
...

str_1 := split_text (v_resourceid1,v_delimeter) ;

for i in 1..str_1.count
   loop
         dbms_output.put_line(str_1(i));
  end loop;

....

END;

Questo se occorre processare un singolo elemento distinto della stringa.

RDBMS ORACLE - SPLIT TEXT with REGEXP

Di seguito un semplice esempio per effettuare lo split di una string utilizzando una regexp.
Supponiamo di avere la seguente stringa:

'COSENZA,MILANO,ROMA,TORINO,NAPOLI'

quello che vogliamo ottenere è una lista :

COSENZA
MILANO
ROMA
TORINO
NAPOLI

Per ottenere questo basta effettuare la seguente query:

SELECT
    regexp_substr('COSENZA,MILANO,ROMA,TORINO,NAPOLI', '[^,]+', 1, level)
FROM
    dual
CONNECT BY
    regexp_substr('COSENZA,MILANO,ROMA,TORINO,NAPOLI', '[^,]+', 1, level) IS NOT NULL;

Il risultato ottenuto sarà il seguente:

REGEXP_SUBSTR('COSENZA,MILANO,ROM
---------------------------------
COSENZA
MILANO
ROMA
TORINO
NAPOLI

Se lo si vuole usare come input in una IN basta riscrivere la query nel seguente modo:

SELECT * FROM CITTA WHERE NAME IN
 (SELECT    regexp_substr('COSENZA,MILANO,ROMA,TORINO,NAPOLI', '[^,]+', 1, level)
    FROM    dual
    CONNECT BY regexp_substr('COSENZA,MILANO,ROMA,TORINO,NAPOLI', '[^,]+', 1, level) IS NOT NULL);
   
 

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.

ODI 12c - Begin/End Mapping Command

Se si vuole utilizzare un pre o post mapping in ODI occorre configurare quanto presente sulle Physical Properties indicando le operazioni che devono essere eseguite.
Di seguito un semplice esempio.

Come primo passo occorre posizionarsi sulla parte bianca del physical e selezionare le proprietà.
Inserire i comandi da effettuare come pre e post mapping in corrispondenza di Begin/End Mapping Command:

Effettuare poi la scelta della tecnologia e della Location su cui deve essere eseguito il comando.

A questo punto avremo:

Come si vede ho inserito dei semplici comandi, il primo di BEGIN verrà eseguito correttamente il secondo di END ho inserito volutamente un errore e quindi in fase di esecuzione a causa dell'END COMMAND il mapping andrà in errore: