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;
Try make call
SELECT DCSDBA.IS_NUMBER('123') FROM DUAL;