oracle function to return list of dates as object
To whom it may respond to ,
I am trying to return list of dates and weekdays to be used in other functions. Code below is compiled without error. But it should give output of 15 days (via V_MAX_DAYS variable) and number of the day in that week.
I have tried to implement like this, but cannot get output using DBMS_OUTPUT. I want to test it but got ORA-06532 error at when running . My aim is to return values to asp.net application as we have done using SYS_REFCURSOR. How can I achieve that? Thank you for your concern,
The script is as below :
CREATE OR REPLACE TYPE DATE_ROW AS OBJECT ( WEEKDAY_VALUE DATE, DATE_IN_LIST VARCHAR2(5) ) / CREATE OR REPLACE TYPE DATE_TABLE as table of DATE_ROW / CREATE OR REPLACE FUNCTION FN_LISTDATES RETURN DATE_TABLE IS V_DATE_TABLE DATE_TABLE := DATE_TABLE (); V_MAX_DAYS NUMBER := 15; V_CALCULATED_DATE DATE; V_WEEKDAY VARCHAR2 (5); BEGIN FOR X IN -2 .. V_MAX_DAYS LOOP SELECT TO_DATE (TO_CHAR (SYSDATE + X, 'DD.MM.YYYY')) INTO V_CALCULATED_DATE FROM 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.
- If you want a DATE (V_CALCULATED_DATE) that is X days from SYSDATE with the time component set to midnight, which appears to be your intent here, you would want something like v_calculated_date := TRUNC(sysdate) + x;. A TO_DATE without an explicit format mask is going to create issues if a future session's NLS_DATE_FORMAT happens not to be DD.MM.YYYY
- If you really want to return a collection like this, your collection indexes would need to start with 1, not -2. You could accomplish that by doing v_date_table(x+3) := DATE_ROW(v_calculated_date, 'Test');.
- However, I would tend to suspect that you would be better served here with a pipelined table function.
The pipelined table function would look something like
SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE FUNCTION FN_LISTDATES 2 RETURN DATE_TABLE 3 PIPELINED 4 IS 5 V_MAX_DAYS NUMBER := 15; 6 V_CALCULATED_DATE DATE; 7 V_WEEKDAY VARCHAR2 (5); 8 BEGIN 9 FOR 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 * from 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.