英文:
Oracle database link to a package saying table not found
问题
我们已经在Oracle 12.1数据库和Oracle APEX 21.2（Oracle 19c）之间创建了一个数据库链接，并授予了一个包的EXECUTE权限，并在函数内的所有列出的表上授予了SELECT权限，该函数被包中调用的函数使用。
但是，在运行调用包函数时，它会显示“表或视图未找到”并给出行号。然而，如果我从数据库链接中执行该提到的行，它可以正常工作。
我尝试授予所有表的SELECT权限以及授予所有从初始包中调用的包的EXECUTE权限。
英文:
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.
答案1
得分: 1
从包内调用的表格不包括内部模式名称，因此您必须为包内调用的每个表格创建一个同义词。
create synonym table_name for schema_name.table_name
因为第二个模式正在执行源模式中的包，所以它会按原样运行代码，但当它尝试运行 select * from table x 时，会显示找不到，因为表 x 只存在于源模式中，所以我们必须指定 source_schema.x，但由于我们无法控制第二个模式的包中的代码，处理的一种方式是创建一个同义词来翻译：
select * from x
到
select * from source_schema.x
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论