We help IT Professionals succeed at work.

Invalid identifier when calling a function in oracle

nguki
nguki asked
on
Hello,
I keep getting the ORA-00904 Invalid identifier error when I call my function in Oracle.
I'm wondering if oracle is being picky with dates and strings.

I have tried multiple combinations with passing the date instead of the string using to_date('01-JUN-2010','DD-MON-YYYY') in my function call and changing the input on the function to (fromdate IN DATE) with no luck

here is the call:
select .., fn_myfunction('01-JUN-2010') as Days_Unread from tablename;

here is my function:
CREATE OR REPLACE  FUNCTION fn_myfunction
 (fromDate IN varchar2
  )
 RETURN NUMBER
IS
countval number;
BEGIN
select count(*) into countval
    from (select rownum rnum
         from tablename
          where rownum <= to_date(sysdate) - to_date(fromDate)+1
          )
    where to_char( to_date(fromDate)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );
    return countval;
END;
Comment
Watch Question

I think this is wrong

where to_char( to_date(fromDate)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );


try

where to_char( to_date(fromDate,'DD/MM/YYYY' )+rnum-1,'DY') not in ( 'SAT', 'SUN' );
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Given your example:  fn_myfunction('01-JUN-2010')

It would be:
where to_char( to_date(fromDate,'DD-<MON-YYYY')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );

Also, sysdate is already a date so there's no need to do:
to_date(sysdate)

So that line should look like (I added ABS just in case it ends up negative):

where rownum <= abs(to_date(sysdate) - to_date(fromDate,'DD-<MON-YYYY')+1 )


Check out the following SQL.  I've hardcoded the dates but you get the idea.
select count(1) from
(
	select to_char(to_date('02/19/2010')+level,'DY') day_of_week
	FROM dual
	CONNECT BY LEVEL < to_date('03/1/2010') - to_date('02/19/2010')
)
where day_of_week not in ('SAT','SUN')
/

Open in new window

Author

Commented:
i tried adding the following:
select count(*) into countval
    from (select rownum rnum
         from tablename
          where rownum <= abs(sysdate - to_date(fromDate,'DD-MON-YYY')+1 )
          )
    where to_char( to_date(fromDate,'DD-MON-YYYY')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );

while the function compiles successfully, when i try to call it using fn_myfunction('01-JUN-2010') i still get the "invalid identifier" error.
Did you try a debug?
there has nothing wrong in this function, I complied and execute in my database. Check the right and any other issue.

some doubt...
1. r u run this function through application or SQL editor?
2. r u run this function in same schema?
Naveen KumarProduction Manager / Application Support Manager

Commented:
may be some typo in your actual code, can you please give your complete without changing anything there.. may be something silly typo there...

1) just try to run a select on that table name without that function all and let us know whether this works or fails ?

2) then include the function call as well in the same select statement and give it a try

I assume that you have all required privileges to access those tables from the schema you are trying to run the select statements.

Thanks,
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
By chance is the column in the table also called fromDate?  If so, the function is trying to use the column name not the variable name.

Check out the following.  I ran it using 10.2.0.3.

drop table tab1 purge;
create table tab1(fromDate date);

insert into tab1 values(sysdate+1);
insert into tab1 values(sysdate-1);
insert into tab1 values(sysdate-2);
insert into tab1 values(sysdate-3);
insert into tab1 values(sysdate-4);
insert into tab1 values(sysdate-5);
insert into tab1 values(sysdate-6);
insert into tab1 values(sysdate-7);
insert into tab1 values(sysdate-8);
insert into tab1 values(sysdate-9);
insert into tab1 values(sysdate-10);
insert into tab1 values(sysdate-11);
insert into tab1 values(sysdate-12);
commit;



CREATE OR REPLACE  FUNCTION myFunc (fromDate_v IN varchar2 ) RETURN NUMBER
IS
	countval number;
BEGIN

	select count(*) into countval
    from (select rownum rnum
         from tab1
          where rownum <= abs(sysdate - to_date(fromDate_v,'DD-MON-YYYY')+1 )
          )
    where to_char( to_date(fromDate_v,'DD-MON-YYYY')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );

    return countval;
END;
/

show errors


CREATE OR REPLACE  FUNCTION myFunc1 (fromDate_v IN varchar2 ) RETURN NUMBER
IS
	countval number;
BEGIN

	select count(1) into countval from
	(
		select to_char(to_date(fromDate_v,'DD-MON-YYYY')+level,'DY') day_of_week
		FROM dual
		CONNECT BY LEVEL < abs(sysdate - to_date(fromDate_v,'DD-MON-YYYY'))+1
	)
	where day_of_week not in ('SAT','SUN');

    return countval;
END;
/

show errors



select myFunc('10-JUN-2010') from dual;
select myFunc1('10-JUN-2010') from dual;

Open in new window

awking00Information Technology Specialist

Commented:
Can you post a describe of your table?

Author

Commented:
I created another test function from what slightwv posted and it worked selecting from the tab1 table.
so I then created another exact same function(as my original one) but with a different name and now it works! very weird.... I was updating the function through Toad and Oracle Java console. Could there be paths that crisscrossed between the two applications?

the old function still throws the same error but the new one works...both are exactly the same except for the name of the function.

thanks for everyone's help!
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>both are exactly the same except for the name of the function.

If that is true then it is VERY weird.  There has to be some real small difference.  Check the parameter names between the two.