Error with cursor in function

Question

I need to create Cursor in a function, but when I execute a function I got an error which is "insufficient privileges" on the table in the Cursor, this table is exist on another schema and I have all the privileges even the select. There is also a synonym for this table on my user, and when I this execute this query outside the Cursor, it works without any problems. I need your help please . Best regards

This is the code:

CREATE OR REPLACE Function cursor_test_func
    ( temp IN number )
    RETURN varchar2
IS
    total_val number(6);

    cursor brn_code_curs is
     SELECT code_brn
     FROM dbschema.branche;

BEGIN
    total_val := 0;
    FOR brn_rec in brn_code_curs
    LOOP
        DBMS_OUTPUT.PUT_LINE ('code = ' || brn_rec.code_brn);
    END LOOP;
    RETURN total_val;
END;

Answer 1

This usually happens if the grant is available via a role but not explicitly for your user. To allow using an object in PL/SQL, your user needs to have the necessary grants.

More information at AskTom.