Thanks for the question, Avik.

Asked: July 07, 2017 - 2:10 pm UTC

Last updated: March 10, 2018 - 5:00 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi TOM (The Oracle Masters),

Thank you for the great guidance you folks have been providing to the Oracle community through this great site !

I have a question regarding Oracle Pipelined functions.

I have created a simple test case for you to check out.

The LiveSQL test case link : https://livesql.oracle.com/apex/livesql/s/e8qpecu5vvr40oh9dklh68lcq

I have created a Pipelined Function that accepts a parameter (let's say of type DATE), and returns a PL/SQL table as resultset.

When I execute that function with some date as parameter (i.e. invoking that function directly with that DATE parameter), it's working fine.

SELECT * FROM TABLE(MY_FUNC(SYSDATE))

I was wondering if there's any way to make this parameter NOT hardcoded during invocation, say getting from a Table cell. Example : I've created a small table SOURCE_DATA in the test case which contains a date column (ST_DATE). Is it possible to execute the pipelined function where it'll accept a cell value from the SOURCE_DATA table (ST_DATE column) instead of me passing the DATE parameter by hand?

I'm trying to achieve Something like

WITH T as (select ST_DATE from SOURCE_DATA where ID = 1)
SELECT * FROM TABLE(MY_FUNC(T.ST_DATE)) ------------

[English is not my native language, so pardon me for any that could've been put in a better way]

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/e8qpecu5vvr40oh9dklh68lcq

and Connor said...

Yes, a function can take a CURSOR as an input, so

SQL> CREATE OR REPLACE FUNCTION MY_FUNC (rc sys_refcursor) RETURN MY_TAB PIPELINED IS
  2    type date_list is table of date index by pls_integer;
  3    l_date_tab date_list;
  4  BEGIN
  5    loop
  6    fetch rc bulk collect into l_date_tab limit 100;
  7      for i in 1 .. l_date_tab.count LOOP
  8        PIPE ROW (MY_OBJECT(i,l_date_tab(i),'Row '||i));
  9      END LOOP;
 10      exit when rc%notfound;
 11    end loop;
 12    RETURN;
 13  END;
 14
 15  /

Function created.

SQL> select *
  2  from table(my_func(cursor(select st_date from SOURCE_DATA)));

         A B         C
---------- --------- -------------------------
         1 01-JUL-17 Row 1
         2 02-JUL-17 Row 2
         3 03-JUL-17 Row 3
         4 04-JUL-17 Row 4
         5 05-JUL-17 Row 5
         6 06-JUL-17 Row 6
         7 07-JUL-17 Row 7
         8 08-JUL-17 Row 8

8 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Maybe OP means this

Anton, July 08, 2017 - 7:46 pm UTC

select mf.*
from source_data sd
, table( my_func( sd.st_date ) ) mf
where sd.id = 1

July 16, 2017 - 7:08 am UTC

we need to see all of your code

Thanks

Avik Dutta, July 10, 2017 - 1:55 pm UTC

Thank you Connor for the response ! As I understand, the function will be able to accept CURSOR as a input, but is it irrespective of whatever datatype the cursor query returns? For example, in our present scenario, ST_DATE is of DATE Type, and it's working fine. However, if the parameter to be passed is of XMLTYPE, will it work too?

To check this I have created another function that accepts an XMLTYPE parameter and "flattens" it to return a pl/table variable. But when I'm trying to pass the XMLTYPE parameter from a table, it throws an error

[The field source_xml is of XMLTYPE in SOURCE_DATA table.]

select * from table(func_xml_to_table(cursor(select source_xml from SOURCE_DATA)));

ORA-06553: PLS-306: wrong number or types of arguments in call to func_xml_to_table.

Is there any other way to pass XMLTYPE to pipelined functions?

July 19, 2017 - 10:57 pm UTC

There is nothing prohibited about xmltypes, eg

SQL> CREATE OR REPLACE FUNCTION MY_FUNC (rc sys_refcursor) RETURN MY_TAB PIPELINED IS
  2    x xmltype;
  3  BEGIN
  4    fetch rc into x;
  5    PIPE ROW (MY_OBJECT(1,x.getstringval(),'blah'));
  6    RETURN;
  7  END;
  8  /

Function created.

Obviously if the date you want is inside the xml, you'd need to parse it out via whatever means you are comfortable with, but there is no inherent restriction on xmltypes

The other way?

John Keymer, March 09, 2018 - 8:42 am UTC

Hi,
Is it possible to do this the other way? For example, if I want to pass the output of a pipelined function to another pipelined function? I know I could pass one row at a time, but I want to pass the full thing. I.e. something like this:

select * from table(my_function(table(dbms_xplan.display_cursor())));

March 10, 2018 - 5:00 am UTC

A pipeline function simply returns rows - so you can wrap it in a cursor.

select * from table(
  cursor(
     select * from my_function(table(dbms_xplan.display_cursor())))
     )
)

(I might not have the brackets right :-))

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Maybe OP means this

Thanks

The other way?

More to Explore

PL/SQL demos

PL/SQL docs

Pipelined Function using Table value as parameter

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Maybe OP means this

Thanks

The other way?

More to Explore

PL/SQL demos

PL/SQL docs