Pipeline Function

Hi

Looking for some help in creating a pipeline function instead of using a ref cursor.  As an example but will expand later, I wish to select on the following with an in param:

in_integerdate_param in number

Select
   First_name,
   Last_name
from
  name_table
where integer_date = in_integerdate_param

Thanks in advance

Andrew
alinesAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:

first you need to create an object to hold each row, then an object to hold the collection that will be piped, then create the function...

CREATE TYPE person_type AS OBJECT
(
    first_name VARCHAR2(100),
    last_name VARCHAR2(100)
);

CREATE TYPE person_table AS TABLE OF person_type;

CREATE OR REPLACE FUNCTION get_names(in_integerdate_param IN NUMBER)
    RETURN person_table
    PIPELINED
AS
BEGIN
    FOR x IN (SELECT first_name, last_name
                FROM name_table
               WHERE integer_date = in_integerdate_param)
    LOOP
        PIPE ROW (person_type(x.first_name, x.last_name));
    END LOOP;

    RETURN;
END get_names;
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.