Re: how to write a function that returns a dataset

From: William Robertson <williamr2019_at_googlemail.com>
Date: Sat, 21 Jul 2007 03:49:19 -0700
Message-ID: <1185014959.338724.138000@o61g2000hsh.googlegroups.com>

On Jul 20, 9:37 am, RBTMEKRDQ..._at_spammotel.com wrote:
> Hello and thanks for all the hints.
>
> DA Morgan wrote:
> > Multiple ways to do it: Try these links for starters:
> >http://www.psoug.org/reference/pipelined.html
> >http://www.psoug.org/reference/ref_cursors.html
>
> yes these examples work, but I don't know how to change them to work
> for my problem.
>
> What I've done until yet:
> -- create a Object type with two values
> CREATE OR REPLACE
> TYPE bankingHolidayType AS OBJECT (bankingholiday DATE, comment
> VARCHAR2(50));
> /
>
> -- create a typeset of the object
> CREATE OR REPLACE
> TYPE bankingHolidayTypeSet AS TABLE OF bankingHolidayType;
> /
>
> -- create a function that uses the typeset
> CREATE OR REPLACE FUNCTION getbankingHolidays(myYear Number)
> RETURN bankingHolidayTypeSet PIPELINED AS
>
> BEGIN
> PIPE ROW(myYear, 'SomeString'); <--- here the compiler says
> "PLS-00103"
> RETURN;
> END date_table;
> /
>
> I don't know how to get the value into the typeset.
>
> In the End I want to create the records with statement like this one:
>
> SELECT TO_DATE('0101'||TO_CHAR(CURRENT_TIMESTAMP, 'YYYY'),
> 'DDMMYYYY'), 'New Yaer' FROM DUAL;
>
> Thanks in Advance
> Ray

First, "COMMENT" is a SQL keyword so you will need to use a different name in your initial type, for example:

CREATE OR REPLACE TYPE bankingHolidayType AS OBJECT ( bankingholiday DATE
, comments VARCHAR2(50) );

Within the function (btw is it called "getbankingholidays" or "date_table"?) where you have

PIPE ROW(myYear, 'SomeString');

that needs to be a bankingHolidayType object, which consists of a DATE (not a number) and a string, so that should be:

PIPE ROW (bankingHolidayType( TO_DATE(myYear||'01','YYYYMM'), 'SomeString'));

(alternatively, change myYear to a date.)

I prefer "p_" for parameter names (and no mixed case for function names - they are not OO objects or classes), so I get this:

CREATE OR REPLACE FUNCTION getbankingholidays

    ( p_year NUMBER )
    RETURN bankingHolidayTypeSet
    PIPELINED
AS
BEGIN
    PIPE ROW
    ( bankingHolidayType

      ( TO_DATE(p_year||'01','YYYYMM')
      , 'SomeString' ) );

    RETURN;
END getbankingholidays;
/

SQL> SELECT * FROM TABLE(getbankingholidays(2007));

BANKINGHOLI COMMENTS

----------- --------------------------------------------------

01-JAN-2007 SomeString

1 row selected. Received on Sat Jul 21 2007 - 05:49:19 CDT