INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Package execution error

Package execution error

(OP)
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;

RE: Package execution error


Packages has "date" parameters:

CODE

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:

CODE

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

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

RE: Package execution error

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close