PL/SQL - execute immediate in pipelined function

Question

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table.

The function:

CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function
  EXECUTE IMMEDIATE v_query;
  --Results of the v_query are compatible with MyTableType's row type. But how to return them from pipelined function?
END;

score 3 · Answer 1 · edited May 23 '17 at 12:15

3

It is possible to combine dynamic SQL and pipelined function but the return type will not be dynamic: the number and type of columns returned will be fixed.

You can use EXECUTE IMMEDIATE with BULK COLLECT (thanks @be here now), dynamic cursors or DBMS_SQL to return more than one row. Here's an example with a dynamic cursor:

SQL> CREATE OR REPLACE PACKAGE pkg AS
  2     TYPE test_tab IS TABLE OF test%ROWTYPE;
  3     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
  3        cc sys_refcursor;
  4        l_row test%ROWTYPE;
  5     BEGIN
  6        OPEN cc FOR 'SELECT * FROM test WHERE ' || l_where;
  7        LOOP
  8           FETCH cc INTO l_row;
  9           EXIT WHEN cc%NOTFOUND;
 10           PIPE ROW (l_row);
 11        END LOOP;
 12        RETURN;
 13     END;
 14  END;
 15  /

Package body created.

Let's call this dynamic function:

SQL> SELECT *
  2    FROM TABLE(pkg.dynamic_cursor('id <= 2'));

        ID DAT
---------- ---
         1 xxx
         2 xxx

As always with dynamic SQL, beware of SQL Injection.

edited May 23 '17 at 12:15

Community

1
1

answered Sep 25 '12 at 08:35

Vincent Malgrat

63,744
9
111
163

@dzb: `DBMS_SQL` won't make the query fast by magic :), in fact there is a little more overhead than in native dynamic SQL. There are so many reasons why your query could be slow that there is no way I can divine how to improve its performance with so little context. The only difference between dynamic and static SQL is the use of variables. Do you use [parameterized dynamic SQL](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#BHCGEFCA)? – Vincent Malgrat Sep 25 '12 at 09:08
Thanks for this example. It's working but horrible slow. I think it is cause I call another pipelined function in my query (I updated my question) and it looks like it's called too many times. Could you post some suggestion to dbms_sql? – dzb Sep 25 '12 at 09:09
1

@VincentMalgrat `execute immediate ... bulk collect into ...; for i in col.first .. col.last loop pipe row () end loop;` ? – Kirill Leontev Sep 25 '12 at 09:10
@dzb: Are the original pipelined functions faster than the wrapper function? – Vincent Malgrat Sep 25 '12 at 09:13
@beherenow You're right, my bad. For some reason I thought you couldn't use `BULK COLLECT` with a table of `%ROWTYPE`. But not only does it work, it will be faster for small sets :) – Vincent Malgrat Sep 25 '12 at 09:18
@Vincent Malgrat: yes, original function is much faster. – dzb Sep 25 '12 at 09:30
@dzb You could try with `BULK COLLECT` instead of a row-by-row loop. Does it improve performance? – Vincent Malgrat Sep 25 '12 at 09:53
@VincentMalgrat: BULK COLLECT works but it's still very slow. I want to have paging of my results which is implemented in my application. But it will not work with this BULK COLLECT approach cause for every page it collects ALL results from inner function. – dzb Sep 25 '12 at 11:03
@dzb there is a [`LIMIT`](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#sthref1018) clause with BULK COLLECT that can be use to retrieve the rows in batch instead of the full dataset – Vincent Malgrat Sep 25 '12 at 11:08
@VincentMalgrat: I tried but it still retrieves all inner function rows (hundreds thousands) when I call my outer function with rownum<100 (for example). – dzb Sep 25 '12 at 11:28

score 1 · Answer 2 · answered Sep 25 '12 at 08:29

I think something like this:

CREATE OR REPLACE FUNCTION MyFunction(par1 VARCHAR2, ...) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
l_result MyTableType;
BEGIN
  v_query := --My query created based on parameters
  EXECUTE IMMEDIATE v_query into l_result;

  pipe row(l_result);
END;

Works only if v_query returns 1 row.

score 0 · Answer 3 · answered Mar 29 '19 at 16:15

I couldn't get @VincentMalgrat's answer to work. But it was very close. Definitely a big help in the right direction for me.

Here's what I got to work:

Package

 CREATE OR REPLACE PACKAGE pkg AS
     TYPE test_row IS RECORD  ( test_name  VARCHAR2 (255), test_number number, test_date date );  
     TYPE test_tab IS TABLE OF test_row;
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
   END;

Package Body

 CREATE OR REPLACE PACKAGE BODY pkg IS
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
        cc sys_refcursor;
        l_row test_row;
     BEGIN
        OPEN cc FOR 'select name_column, number_column, date_column FROM my_table where number_column ='||l_where;
        LOOP
           FETCH cc INTO l_row;
           EXIT WHEN cc%NOTFOUND;
           PIPE ROW (l_row);
        END LOOP;
        RETURN;
     END;
  END;

PL/SQL - execute immediate in pipelined function

3 Answers3

Linked