Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> need help: ORA-902 err in PL/SQL function
Hi.
I have created a medium sized PL/SQL function that generates table entries for me based on dates, etc of other entries. I find that when I run the following SQL-PLUS command:
SELECT GEN_CR_INVOICES(SYSDATE,SYSDATE) FROM DUAL; that I get ORA-00902 ('datatype entered in CREATE or ALTER TABLE statement is not valid.') This has completely stopped my work, and the folks at Oracle Support can't find anything wrong with the function. Does anyone have any ideas? The function does not issue a CREATE TABLE or ALTER TABLE statement! (but I wonder if it's a problem with it's cursor).
Thanks in advance,
Jim Mitchell
PS: For any guru's out their that are either saintly or masochistic enough to want to see my code, here 'tis:
0,0,0,cre_fee,cre_fee,'N','Net 30','PACKAGE: '||PACK_NAME||
DECODE(NVL(CREATIVE_MIN,0),0,'',CHR(13)||CHR(10)||
'**$'||TO_CHAR(CREATIVE_MIN,'fm999,999,999,990.00')||'
MIN.**'),
PACK_ID||TO_CHAR(TRUNC(end_date))
FROM PACKAGE, RDI_CLIENT
WHERE PACKAGE.PK_LINK = pk_key AND
PACKAGE.CL_LINK = RDI_CLIENT.CL_LINK;
INSERT INTO CR_INVOICES
VALUES (inv_key,NULL,pk_key,descrip,quantity);
END;
/
CREATE OR REPLACE FUNCTION GEN_CR_INVOICES (
begin_date DATE,
end_date DATE)
RETURN NUMBER
IS
started CHAR(1); -- have we gotten any valid data yet? descrip VARCHAR2(400); -- textual list of mailcodes and qtys quantity NUMBER; -- total qty mailed for package mailcode VARCHAR2(20); -- individual mailcode from cursor mailqty NUMBER; -- individual mailcode qty from cursor pkg VARCHAR2(20); -- the pack_id field from cursor curr_package VARCHAR2(20); -- current package processing packages_invoiced NUMBER; -- return val: invoices produced pk_key VARCHAR2(10); -- key value of package table CURSOR c IS SELECT RDI_MAILCODE.MAIL_CODE, RDI_MAILCODE.DROP_QTY, PACKAGE.PACK_ID, PACKAGE.PK_LINK FROM RDI_MAILCODE, PACKAGE WHERE RDI_MAILCODE.DROP_QTY IS NOT NULL AND RDI_MAILCODE.PK_LINK = PACKAGE.PK_LINK AND TRUNC(RDI_MAILCODE.DROP_DATE) >= TRUNC(begin_date) AND TRUNC(RDI_MAILCODE.DROP_DATE) <= TRUNC(end_date) AND NOT EXISTS (SELECT INV_TAG FROM INVOICES WHERE INV_TAG = PACKAGE.PACK_ID||TO_CHAR(TRUNC(end_date)) ) ORDER BY PACKAGE.PACK_ID;