need help: ORA902 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:

• Functions to create Creative Invoices CREATE OR REPLACE PROCEDURE INVOICE_CREATIVE( pk_key VARCHAR2, descrip VARCHAR2, quantity NUMBER, begin_date DATE, end_date DATE) IS cre_fee NUMBER; -- creative fee inv_key CHAR(4); -- PK of INVOICES BEGIN
  • calculate the creative fee SELECT quantity*TO_NUMBER(MISC_VALUE)/1000 INTO cre_fee FROM MISC_CONSTANTS WHERE MISC_NAME = 'CREATIVE_RATE_M';
  • get primary key to insert into invoices table SELECT TO_CHARLINK(INVOICES_SQ.NEXTVAL) INTO inv_key FROM DUAL;
  • Create the invoice INSERT INTO INVOICES SELECT inv_key, 'CR', CREATIVE_INVOICE_SQN.NEXTVAL, PACKAGE.CL_LINK, TRUNC(begin_date),TRUNC(end_date),TRUNC(SYSDATE),cre_fee,

        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;

• init vars descrip := ''; quantity := 0; curr_package := ''; started := 'N'; packages_invoiced := -1; OPEN c;
• for each package extant, for each mailcode between the
• dates selected, sum the drop quantities, building a
• summary description as well as a running total. Insert
• the pertinant records into the invoice table, with
• accompanying records into the cr_invoices table IF c%ISOPEN THEN packages_invoiced := 0; -- no error condition from cursor failure LOOP FETCH c INTO mailcode, mailqty, pkg, pk_key; IF c%FOUND THEN -- there's data IF pkg <> curr_package THEN IF started = 'N' THEN started := 'Y'; ELSE -- invoice the package data invoice_creative(pk_key,descrip, quantity, begin_date,end_date); packages_invoiced := packages_invoiced +1; END IF; descrip := ''; -- get new description field quantity := 0; -- start qty over curr_package := pkg; -- remember current package ELSE -- more data, put comma in descrip descrip := descrip || ', '; END IF; descrip := descrip|| RTRIM(mailcode)||' ('||TO_CHAR(mailqty) ||')'; quantity := quantity + mailqty; ELSE -- write last data and exit IF started = 'Y' THEN invoice_creative(pk_key,descrip, quantity,begin_date,end_date); packages_invoiced := packages_invoiced +1; END IF; CLOSE c; EXIT; -- end loop END IF; -- {c%FOUND} END LOOP; END IF; -- {open cursor} RETURN packages_invoiced; END; /