oracle function to return list of dates as object -

to whom may respond ,

i trying return list of dates , weekdays used in other functions. code below compiled without error. should give output of 15 days (via v_max_days variable) , number of day in week.

i have tried implement this, cannot output using dbms_output. want test got ora-06532 error @ when running . aim return values asp.net application have done using sys_refcursor. how can achieve that? thank concern,

the script below :

create or replace type date_row object (   weekday_value date,   date_in_list varchar2(5) ) /  create or replace type date_table table of date_row /    create or replace function fn_listdates    return date_table    v_date_table        date_table    := date_table ();    v_max_days          number        := 15;    v_calculated_date   date;    v_weekday           varchar2 (5); begin    x in -2 .. v_max_days    loop       select to_date (to_char (sysdate + x, 'dd.mm.yyyy'))         v_calculated_date         dual;       v_date_table.extend;       v_date_table(x) := date_row(v_calculated_date, 'test');    end loop;    return v_date_table; end; / 

a few points.

1. if want date (v_calculated_date) x days sysdate time component set midnight, appears intent here, want v_calculated_date := trunc(sysdate) + x;. to_date without explicit format mask going create issues if future session's nls_date_format happens not dd.mm.yyyy
2. if want return collection this, collection indexes need start 1, not -2. accomplish doing v_date_table(x+3) := date_row(v_calculated_date, 'test');.
3. however, tend suspect better served here pipelined table function.

the pipelined table function like

sql> ed wrote file afiedt.buf    1  create or replace function fn_listdates   2     return date_table   3     pipelined   4    5     v_max_days          number        := 15;   6     v_calculated_date   date;   7     v_weekday           varchar2 (5);   8  begin   9     x in -2 .. v_max_days  10     loop  11        v_calculated_date := trunc(sysdate) + x;  12        pipe row( date_row(v_calculated_date,'test') );  13     end loop;  14     return;  15* end; sql> /  function created.  sql> select * table( fn_listdates );  weekday_v date_ --------- ----- 30-nov-10 test 01-dec-10 test 02-dec-10 test 03-dec-10 test 04-dec-10 test 05-dec-10 test 06-dec-10 test 07-dec-10 test 08-dec-10 test 09-dec-10 test 10-dec-10 test  weekday_v date_ --------- ----- 11-dec-10 test 12-dec-10 test 13-dec-10 test 14-dec-10 test 15-dec-10 test 16-dec-10 test 17-dec-10 test  18 rows selected.