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.