Re: how to write a function that returns a dataset

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 Received on Fri Jul 20 2007 - 03:37:34 CDT