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.

Un pensiero su “HOWTO: get a recordset result from a PL/SQL Function

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...