Recentemente ho avuto a che fare con chiamate a Stored Function Oracle da una applicazione web basata su JPA come framework di persistenza. Purtroppo questo tipo di operazione non è coperto dalla specifica JPA, per cui bisogna ricorrere alle funzionalità specifiche del vendor che abbiamo scelto. Usando EclipseLink (ex Oracle TopLink) diciamo che si gioca in casa, anche se le cose si fanno molto complesse quando i parametri da scambiare con la funzione sono tipi complessi come Object Types o Varying Array (varray). La documentazione a riguardo infatti è caotica e frammentata in rete. Ecco come ne sono venuto fuori..

La funzione lato Database Oracle

La funzione che proveremo a chiamare, definita all’interno di un package Oracle, avrà la seguente specification

FUNCTION MY_FUNCTION(SIMPLE_PARAM    VARCHAR2, 
                     COMPLEX_PARAM   MY_TABLE_OF_RECORDS) RETURN NUMBER;

possiamo notare che il primo parametro è di tipo semplice, mentre il secondo è un Type di Oracle, nello specifico un Array di Objects, per rendere le cose più complesse!

CREATE OR REPLACE TYPE MY_RECORD AS OBJECT (
   CODE          NUMBER
   DESCRIPTION   VARCHAR2(1000)
);

CREATE OR REPLACE TYPE MY_TABLE_OF_RECORDS AS TABLE OF MY_RECORD;

I tipi sono definiti come SQL Types e non come PL/SQL Types: se così fosse, bisognerebbe creare i rispettivi tipi “specchio” SQL, perché il driver di Oracle non sarebbe capace di gestirli direttamente.

La funzione lato Java

La prima cosa da fare lato Java è creare una classe che mappa il tipo MY_RECORD

public class MyRecord implements Serializable {
   private int code;

   private String description;
  
   //Gettes & Setters
}

Quello che manca è come specificare il mapping. Purtroppo non ho potuto seguire il wiki di EclipseLink a rigurardo perché, nonostante abbia la versione 2.3, non ho le annotazioni che vengono menzionate.

Dobbiamo allora rimboccarci le maniche e mappare “manualmente” la classe con il tipo SQL, creando il descrittore seguente:

private ClassDescriptor buildMyRecordMapping() {
   ObjectRelationalDataTypeDescriptor myRecordDesc = new ObjectRelationalDataTypeDescriptor();
   myRecordDesc.descriptorIsAggregate();
   myRecordDesc.setJavaClass(MyRecord.class);
   myRecordDesc.setAlias("my_record");
   myRecordDesc.setStructureName("MY_RECORD");

   DirectToFieldMapping codeMapping = new DirectToFieldMapping();
   codeMapping.setAttributeName("code");
   codeMapping.setFieldName("CODE");
   myRecordDesc.addMapping(codeMapping);

   DirectToFieldMapping descriptionMapping = new DirectToFieldMapping();
   descriptionMapping.setAttributeName("description");
   descriptionMapping.setFieldName("DESCRIPTION");
   myRecordDesc.addMapping(descriptionMapping);
}

Ovviamente in un uso reale con un po’ di annotazioni (per esempio @Column) e un po’ di reflection, possiamo generalizzare questo codice boilerplate.

Impostare la chiamata alla funzione

L’API di EclipseLink per chiamare le stored functions è abbastanza verbosa perché necessita di:

• istanziare una DatabaseCall: nel caso specifico sarà una StoredFunctionCall nella quale verrà specificato il nome della funzione Oracle da chiamare. Per quanto riguarda i suoi parametri in ingresso (se esistono), è possibile gestirli in due modalità:
  • Named Arguments (parametri nominali): si specifica il nome del parametro (come definito nella funzione) e il suo valore. Solo i tipi semplici (stringhe, numeri…) possono essere passati in questa modalità.
  • Unnamed Arguments (parametri anonimi/posizionali): i parametri vengono passati in modo posizionale rispetto alla loro dichiarazione nella funzione, per cui deve essere specificato un alias per identificare il parametro stesso. Il valore in questo caso non può essere passato alla call. Nel caso di parametri complessi questo è l’unico modo per lavorare.
• istanziare una DatabaseQuery: a seconda del tipo di operazione svolta dalla funzione, sarà del tipo DataReadQuery o DataModifyQuery. Nel caso di Unnamed Arguments specificati nella call, la query deve definire gli stessi “arguments” con gli stessi alias dichiarati nello stesso ordine in modo da associarvi i valori.

Quindi, riassumendo: in caso di Named Arguments, i valori dei parametri vengono passati alla call; nel caso di Unnamed Arguments, i valori vengono passati alla query (che deve ridefinire gli stessi alias della call nello stesso ordine) … facile no?! 🙁
Nel caso di parametri complessi, siamo costretti a passare i parametri in modo anonimo (o posizionale se preferite) come segue:

StoredFunctionCall call = new StoredFunctionCall();
call.setProcedureName("MY_PACKAGE.MY_FUNCTION");
call.addUnamedArgument("PARAM_1");
call.addUnamedArgument("PARAM_2", Types.ARRAY, "MY_TABLE_OF_RECORDS", getStructDatabaseField());
call.setResult("RESULT", Integer.class);

DataReadQuery query = new DataReadQuery();
query.setShouldBindAllParameters(true);
query.bindAllParameters();
query.setResultType(DataReadQuery.VALUE);
query.addArgument("PARAM_1");
query.addArgumentValue("Valore parametro 1");
query.addArgument("PARAM_2");
query.addArgumentValue(myJavaArray);
query.setCall(call);

serverSession.addDescriptor(buildMyRecordMapping());
serverSession.executeQuery(query);

Cerchiamo di capire i passaggi:

1. Creiamo un’istanza di StoredFunctionCall per descrivere la firma della funzione, nella quale si specificano i parametri di ingresso (e il tipo se complesso), e il tipo di ritorno, con il nome del parametro a cui verrà legato.
2. La riga 4 è quella che permette di far funzionare tutto. Viene specificato l’alias del parametro, il tipo SQL, il nome del tipo SQL e un descrittore che specifica di che tipo è fatto l’array come segue:
   

   private ObjectRelationalDatabaseField getStructDatabaseField() {
      ObjectRelationalDatabaseField databaseField = new ObjectRelationalDatabaseField("");
      databaseField.setSqlType(Types.STRUCT);
      databaseField.setSqlTypeName("MY_RECORD");
      databaseField.setType(MyRecord.class);
   }
   

   
   Nel caso in cui il parametro della funzione fosse stato un type e non un array di types, questo passo poteva essere leggermente più semplice:
   

   call.addUnamedArgument("PARAM_2", Types.STRUCT, "MY_RECORD");
   

3. Creiamo un’istanza di DataReadQuery (per esempio se la funzione non modifica dati) e aggiungiamo gli alias dei parametri nello stesso ordine in cui sono stati aggiunti nella call. Anche i valori dei parametri devono rispettare lo stesso ordine. La variabile myJavaArray sarà di tipo MyRecord[].
4. A questo punto rimangono due cose: associare il mapping di MyRecord alla ServerSession di EclipseLink (perché è stato creato programmaticamente da noi) ed eseguire la query, a cui abbiamo precedentemente associato la call.

Conclusioni

Configurare una chiamata ad una stored function richiede, come abbiamo visto, una serie di passaggi che non sono banali e che richiedono una certa precisione nell’ordine in cui vengono effettuati. In un ambiente di lavoro questo codice merita di essere rifattorizzato, magari con un adapter, per nascondere un po’ di verbosità e un po’ di ripetizioni: per motivi esemplificativi però questa forma risulta sicuramente più chiara.