HOWTO: get a recordset result from a PL/SQL Function

 Questo articolo è disponibile anche in italiano.

How to get a recordset result from a parametric query in PL/SQL in one command?

At first, I thought:

  • It should be useful to use a view but…. We can’t “pass” parameters to a view; a view could read some information from the SYS_CONTEXT or from a table where to store the parameter, but in this way the commands used would be complicated ad we should involve a transaction, isolation of the parameter etc.
  • I may use a procedure but… A procedure can’t return a recordset; it can return a cursor, that cannot be directly “viewed” but rather used by another procedure or PL/SQL code that loops thorugh it.
So, here comes the solution. We may use OBJECT TYPES and PIPELINED FUNCTIONS.
A pipelined function, can return a collection of objects, each of which will be rendered as a record of a table in the resulting recordset; so we have to define this object and the relative collection object in PL/SQL:
    CREATE OR REPLACE
    TYPE O_MY_OBJECT AS OBJECT (
      MY_DATE                       DATE
    , MY_NUMBER                     NUMBER
    , MY_VARCHAR                    VARCHAR2(20)
    );

    CREATE OR REPLACE
    TYPE T_MY_OBJECT AS TABLE OF O_MY_OBJECT;
Then, we can declare a function as PIPELINED and say it will return the previously defined collection type and, in the function body, create the objects (the record) and send them to the output with PIPE ROW():
    FUNCTION MY_FUNCTION(V_MY_PARAM IN VARCHAR2)
      RETURN  T_MY_OBJECT
      PIPELINED
    AS
    V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null);
    BEGIN
          SELECT
                SYSDATE
              , 10
              , 'CIAO MONDO! -> ' || V_MY_PARAM
          INTO
              V_MY_OBJ_VAR.MY_DATE
            , V_MY_OBJ_VAR.MY_NUMBER
            , V_MY_OBJ_VAR.MY_VARCHAR
          FROM
              DUAL;

          PIPE ROW(V_MY_OBJ_VAR);

      RETURN;
    END MY_FUNCTION;
Let’s go deep in some detail:
  • V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null); => I define a variable and init it with the object’s default constructor
  • INTO clause => set the desired values (in this case some constant value) in my object’s properties
  • PIPE ROW(V_MY_OBJ_VAR) => send the object to output
If we have to work with rows returned by a parametric query that we want to execute inside the pipelined function, we can declare and LOOP through a cursor and, at every loop cycle, build a new object instance and send it to the output with PIPE ROW.
In the end, to call this function and get the final result, we must use the PL/SQL TABLE function:
     SELECT * FROM TABLE(MY_FUNCTION('SAMPLE VALUE'));
The result, will be a grid of results usable as a table or a view.

Ottenere un recordset da una Function PL/SQL

 This article is available in english language too.

Tempo fa sono incappato in un problema con Oracle. Come ottenere un recordset da una query parametrica in PL/SQL in un solo comando?

Le prime risposte cui si potrebbe pensare sono:

  • Uso una vista a cui passo un paramtero: non si possono passare parametri ad una vista; una vista potrebbe al massimo leggere un parametro dal SYS_CONTEXT o da una tabella di appoggio, ma in questo modo non si riuscirebbe in un solo comando ad ottenere il risultato
  • Uso una procedure; una procedure non può restituire un recordset; l’unico oggetto simile che può restituire è un cursore, che più che essere “visualizzato” dovrebbe essere poi utilizzato da una qualche altra procedura (PL/SQL o client che sia)
La tipologia di oggetto che risolve questo specifico problema è l’uso degli OBJECT TYPES e delle FUNCTION PIPELINED.
Una funzione pipelined può restituire una collezione di oggetti ognuno dei quali verrà restituito come un record nel recordset risultante; per definire un nuovo oggetto e la relativa collezione in PL/SQL avremo:
    CREATE OR REPLACE
    TYPE O_MY_OBJECT AS OBJECT (
      MY_DATE                       DATE
    , MY_NUMBER                     NUMBER
    , MY_VARCHAR                    VARCHAR2(20)
    );

    CREATE OR REPLACE
    TYPE T_MY_OBJECT AS TABLE OF O_MY_OBJECT;
Quindi, una volta definiti questi due tipi, si può dichiarare una funzione PIPELINED che restituisca il tipo collezione e nel suo body creare gli oggetti (i record) e mandarli in output con l’istruzione PIPE ROW():
    FUNCTION MY_FUNCTION(V_MY_PARAM IN VARCHAR2)
      RETURN  T_MY_OBJECT
      PIPELINED
    AS
    V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null);
    BEGIN
          SELECT
                SYSDATE
              , 10
              , 'CIAO MONDO! -> ' || V_MY_PARAM
          INTO
              V_MY_OBJ_VAR.MY_DATE
            , V_MY_OBJ_VAR.MY_NUMBER
            , V_MY_OBJ_VAR.MY_VARCHAR
          FROM
              DUAL;

          PIPE ROW(V_MY_OBJ_VAR);

      RETURN;
    END MY_FUNCTION;
Vediamo i particolari della funzione:
  • V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null); => Definisco una variabile e la inizializzo con il costruttore dell’oggetto
  • Parte INTO => inserisco i valori desiderati (in questo caso delle costanti) nelle proprietà del mio oggetto
  • PIPE ROW(V_MY_OBJ_VAR) => mando in output al recordset il mio oggetto
Per avere righe che vengono da una query parametrica, è sufficiente costruire un cursore e in un LOOP costruire le istanze di oggetti di cui si farà il PIPE ROW.
Infine per richiamare la funzione così costruita ed avere il risultato finale, è necessario utilizzare la funzione TABLE di PL/SQL:
     SELECT * FROM TABLE(MY_FUNCTION('SAMPLE VALUE'));
Il risultato, sarà una griglia in tutto e per tutto utilizzabile come una tabella, o una vista.