Re: how to write a function that returns a dataset

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 20 Jul 2007 05:46:28 -0700
Message-ID: <1184935587.633499@bubbleator.drizzle.com>

RBTMEKRDQKDL_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

Put the values into a table and follow the StockTicker example.

If you want to assign them to the array directly look at the example that generates dates.

If posting error message include the full message text as the same number may have different text and always your version number.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Jul 20 2007 - 07:46:28 CDT