We've created a database link from an Oracle 12.1 DB to Oracle APEX 21.2 (Oracle 19c) and have granted EXECUTE to a package and granted SELECT to all the listed tables inside the function that's used by the function called in the package.
But when running calling the package function, it says table or view not found and gives the line number. However, if I execute that mentioned line from the database link, it works fine.
I tried granting SELECT to all the tables and EXECUTE to all the packages getting called from the initial package.
The tables getting called from inside the package don't include the internal schema name, so you have to create a synonym for each table that is being called in the package.
create synonm table_name for schema_name.table_name
Because the second schema is executing a package from the source schema, it runs the code as is but when it tries to run select * from table x, it'll say not found because table x only exists in the source schema so we'll have to specify source_schema.x but since we can't control the code from the package from the second schema, one way to handle it is to create a synonym to translate
select * from x
to
select * from source_schema.x