Package execution error

[CrystalProgDev]

I have a package with pipelined function in it. using the below stmt to execute the function.

select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));

Throwing an error: invalid month error. Can any one please let me know where I am doing wrong.

CREATE TYPE WBS_ROW IS OBJECT
(
Product VARCHAR2 (100),
DESC VARCHAR (1000),
RecDATE DATE);

CREATE TYPE WBS_TAB IS TABLE OF WBS_ROW;

CREATE OR REPLACE PACKAGE "test_PKG"
AS
FUNCTION Test_PIPELINED (Val VARCHAR2, PERIOD VARCHAR2,
STARTDATE DATE,
ENDDATE DATE)
RETURN WBS_TAB
PIPELINED;
END Test_PKG;

CREATE OR REPLACE PACKAGE BODY "Test_PKG"
AS
FUNCTION Test_PIPELINED (Val VARCHAR2, PERIOD VARCHAR2,
STARTDATE DATE,
ENDDATE DATE)
RETURN WBS_TAB
PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR WBS_CUR
IS
SELECT P.Product AS "Product",
P.VDESC AS "DESC",
TO_CHAR (P.DATE2_ADD, 'MM/DD/RRRR') AS "RecDATE"
FROM Product p
WHERE ( (PERIOD = 'ALL')
OR ( P.RecDATE >=
DECODE (
PERIOD,
'DAY', TRUNC (SYSDATE, 'DD') - 1,
'WEEK', TRUNC (SYSDATE, 'DD') - 7,
'MONTH', ADD_MONTHS (
TRUNC (SYSDATE, 'MM'),
-1),
'QUARTER', ADD_MONTHS (
TRUNC (SYSDATE, 'Q'),
-3),
'YEAR', ADD_MONTHS (
TRUNC (SYSDATE, 'YYYY'),
-12),
'--N/A--', TO_DATE (STARTDATE,
'MM/DD/YYYY'))
AND P.RecDATE <
DECODE (
PERIOD,
'DAY', TRUNC (SYSDATE, 'DD'),
'WEEK', TRUNC (SYSDATE, 'DD'),
'MONTH', TRUNC (SYSDATE, 'MM'),
'QUARTER', TRUNC (SYSDATE, 'Q'),
'YEAR', TRUNC (SYSDATE, 'YYYY'),
'--N/A--', TO_DATE (ENDDATE,
'MM/DD/YYYY')
+ 1)));
BEGIN
FOR RS_ROW IN WBS_CUR
LOOP
PIPE ROW (WBS_ROW (RS_ROW.Product,
RS_ROW.DESC,
RS_ROW.RecDATE));
END LOOP;

RETURN;
END;
END;

 

[LKBrwnDBA]

Packages has "date" parameters:

CREATE OR REPLACE PACKAGE BODY "Test_PKG"
AS
FUNCTION Test_PIPELINED (Val VARCHAR2, PERIOD VARCHAR2,
STARTDATE DATE,
ENDDATE DATE)
RETURN WBS_TAB
PIPELINED
IS
. . .

But these are NOT dates:

. . . ,'1/1/2010','1/1/2011'));

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[khobar]

Hi

try using to_date('01/01/2010','dd/mm/yyyy') or simply pass the date in dd-mon-yyyy format and see if it helps

khobar