CODEWITHSUNDEEP
oracle-databaseplsql
ngundling
ngundling

Reputation: 17

ORA 904 - Error compiling function that references function from another schema - has execute privilege

I am getting a "ORA-00904 invalid identifier" error trying to use a function from another schema inside a function in my schema. I am using the fully qualified name of the function, and have execute privilege on the function through a granted role.

Executing the function directly from my schema works. But calling that function from a function in my schema doesn't, and gives the 904 error.

Example:

I am trying to compile a procedure called proc_A in a schema called schema_A.

proc_A uses the output of a function called proc_B from schema_B.

schema_A has access to proc_B through a role. schema_A is able to run proc_B directly, but is cannot use it in a function/procedure. Using it in a procedure causes a compilation failure.

Below is a code sample that demonstrates the problem:

CREATE OR REPLACE FUNCTION schema_b.proc_b
RETURN  DATE IS
BEGIN
    RETURN sysdate;
END;
/


CREATE ROLE run_query
/


GRANT EXECUTE ON schema_b.proc_b TO run_query
/
GRANT run_query TO schema_a
/



-- this will fail to compile with the ORA-904 invalid identifier error
CREATE OR REPLACE FUNCTION schema_a.proc_a
RETURN NUMBER IS
BEGIN
    FOR c IN (SELECT schema_b.proc_b() FROM dual) LOOP --> this function call causes the error
        NULL;
    END LOOP;
    RETURN 5;
END;
/

Running Oracle 11g R2

Upvotes: 0

Views: 868

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

The privilege must be direct (not granted to a ROLE).

If you change the grant from:

GRANT EXECUTE ON schema_b.proc_b TO run_query
/

To:

GRANT EXECUTE ON schema_b.proc_b TO schema_a;
/

The dependent procedure in schema_a should compile.

Upvotes: 0

Related Questions