sqloraclefunctionora-00904

I get "ORA-00904: invalid identifier" on function call despite being given GRANT EXECUTE


I am trying to make a call to a IS_NUMBER function for the DSCRPT user name, but I've had to create the function in the DSCDBA login I GRANTed EXECUTE permissions as I have on multiple other functions, but this one is raising an INVALID IDENTIFIER error

Function:

create or replace 
FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END;

GRANT:

GRANT EXECUTE ON "DCSDBA"."IS_NUMBER" TO "DCSRPT";

Executing SQL while logged in as DSCRPT

SELECT IS_NUMBER('123') FROM DUAL;

Error:

ORA-00904: "IS_NUMBER": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 1 Column: 8

Confirmed I can see the function while logged into DSCRPT

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION') and owner = 'DCSDBA' order by owner, object_name;

Return of ALL_OBJECTS search


Solution

  • Try make call

    SELECT DCSDBA.IS_NUMBER('123') FROM DUAL;