Re: need help: ORA902 in PL/SQL function

jmitchell_at_jimdesu.com (Jim Mitchell) wrote:

>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

It might be because you're calling the function from a select.

Try it this way: (from sqlplus 3.2 or later)

VARIABLE test
BEGIN
:test := GEN_CR_INVOICES(SYSDATE,SYSDATE); END;
/
PRINT test

This would at least eliminate any flakyness of selecting from dual;

• Mike Madland mikemad_at_asymetrix.com