尝试访问另一个模式表时获取 PLS-00201 [英] Getting PLS-00201 when trying to access another schema table

问题描述

我想清除 SCHEMA_ADM 和 SCHEMA_DAT 表上的一些数据，但是，我的过程需要存储在 SCHEMA_DAT 上并且将由 SCHEMA_APP 调用.我做了这样的程序:

I want to clean some data on SCHEMA_ADM and SCHEMA_DAT tables, but, my procedure need to be stored on SCHEMA_DAT and will be called by SCHEMA_APP. I did the procedure like this:

create or replace PACKAGE BODY "PKG_CLEANING_THE_MESS"
AS
PROCEDURE PRC_DELETE_DATA (some variables...)
AS
    TYPE fooTableType
    IS TABLE OF FOO_TABLE.ID%TYPE; --TABLE ON SCHEMA_DAT. Works fine!
    fooTable fooTableType;

    TYPE xyzTableType
    IS TABLE OF XYZ_TABLE.ID%TYPE; --TABLE ON SCHEMA_ADM. The problem is here!!!
    xyzTable xyzTableType;
    ...
END PRC_DELETE_DATA;
...
END PKG_CLEANING_THE_MESS;

当我尝试编译时，给我

PLS-00201:必须声明标识符XYZ_TABLE".

PLS-00201: identifier 'XYZ_TABLE' must be declared.

我尝试了SCHEMA_ADM".XYZ_TABLE"而不是XYZ_TABLE"，但也没有奏效.SCHEMA_DAT 对 SCHEMA_ADM 具有 SIUD 角色，对 XYZ_TABLE 具有 SYNONYM，但在研究中我发现存储过程不能使用角色授予.但是我没有找到任何方法来授予 PROCEDURE 上的授权以无错误地编译并保证 SCHEMA_APP 也可以调用该过程.有什么帮助吗?谢谢！

I tried "SCHEMA_ADM"."XYZ_TABLE" instead of just "XYZ_TABLE", but didn't works too. The SCHEMA_DAT has a SIUD role to SCHEMA_ADM and a SYNONYM to XYZ_TABLE, but doing research I found that stored procedures can't use the grant of roles. But I didn't found any way to give the grants on the PROCEDURE to compile without errors and to guarantee that SCHEMA_APP also can call the procedure. Any help on that? Thanks!

我发现了这个类似的问题，4 年前问过，但没有确定的答案:/

I found this similar question, asked 4 years ago, but without a conclusive answer :/

推荐答案

此错误的检查清单应类似于:

The checklist for this error should be something like:

1. 表 XYZ_TABLE 是否存在?(你拼对了吗?它实际上是用双引号命名的，例如 "xyz_table" 或 "XYZ Table"?)
2. 它是否在预期的架构中，SCHEMA_ADM?
3. SELECT 等权限是否直接授予包所有者SCHEMA_DAT?
4. 预期名称是否有私有或公共同义词?(例如，表 Y 可能有同义词 X，但代码指的是 Y.)
5. 或者，是在它前面加上模式名称的代码，例如SCHEMA_DAT.XYZ_TABLE?
6. 是否存在与架构 (SCHEMA_DAT) 同名的包或类型?这可能会造成命名冲突，编译器会在其中找到包而不是模式，然后在其中查找名为 XYZ_TABLE 的内容.

1. Does table XYZ_TABLE exist? (Did you spell it right? Is it actually named in double-quotes as something like "xyz_table" or "XYZ Table"?)
2. Is it in the expected schema, SCHEMA_ADM?
3. Are privileges such as SELECT granted directly to the package owner, SCHEMA_DAT?
4. Is there a private or public synonym with the expected name? (e.g. there might be a synonym X for table Y, but the code refers to Y.)
5. Or, is the code prefixing it with the schema name e.g. SCHEMA_DAT.XYZ_TABLE?
6. Is there a package or type with the same name as the schema (SCHEMA_DAT)? This could create a naming conflict where the compiler finds the package instead of the schema, and then looks in that for something called XYZ_TABLE.

您的要求可能会有所不同，但创建同义词通常比硬编码架构名称要好(无需键入，如果情况发生变化则更灵活)，并且私有同义词比公共同义词更可取(安全性，因为它们没有广播您的架构结构，如果您想将不同的用户定向到不同的对象，或者稍后添加具有不同要求的另一个架构，则更加灵活).

Your requirements may vary, but it's usually better to create a synonym than to hardcode the schema name (less to type, more flexibility if things change), and private synonyms are preferable to public synonyms (security, as they don't broadcast your schema structure, and more flexible in case you want to direct different users to different objects, or add another schema later with different requirements).

这篇关于尝试访问另一个模式表时获取 PLS-00201的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！