使用另一个架构中的表创建存储过程会引发PLS-00201 [英] Create stored procedure with table from another schema throws PLS-00201

问题描述

Oracle 10g.我是过程的新手，所以也许我缺少明显的东西.

Oracle 10g. I'm new to procedures, so maybe I'm missing something obvious.

模式所有者ABC具有表T2001_WRITEOFF.首先，我将SIUD授予some_update_role，并将该角色授予开发人员用户IJK.然后，用户IJK为ABC.T2001_WRITEOFF创建了同义词T2001_WRITEOFF;这适用于普通的SQL DML命令.

Schema owner ABC has table T2001_WRITEOFF. First I had granted SIUD to some_update_role, and granted that role to developer user IJK. User IJK then created synonym T2001_WRITEOFF for ABC.T2001_WRITEOFF; This worked with normal SQL DML commands.

但是，我在这里的其他地方读到，通过角色进行的授予在存储过程中不起作用.我放弃了IJK的同义词；然后从美国广播公司(ABC)直接将SIUD授予IJK.通过IJK，正常的SQL DML可以正常工作.

However, I read elsewhere on here that grants via a role do not work in stored procedures. I dropped the synonym from IJK; then from ABC, granted SIUD directly to IJK. From IJK, normal SQL DML works.

当我尝试如下创建一个简单过程时，它会抛出PLS-00201标识符'T2001_WRITEOFF'，并且必须指向第二行.无论我是否使用角色授予，此错误都是相同的.

When I try to create a simple procedure as follows, it throws PLS-00201 identifier 'T2001_WRITEOFF' must be declared, and points to the 2nd line. This error is the same whether I use the role grants or not.

create or replace procedure woof1(
    fooname in T2001_WRITEOFF.territory%TYPE,  <=== error points here
    bardesc IN T2001_WRITEOFF.ind_batch_submit%TYPE) IS
BEGIN
    INSERT into T2001_WRITEOFF
    VALUES ( fooname, bardesc);
END woof1;
/

预先感谢您的帮助

JimR

推荐答案

为了使角色权限适用于存储过程，您可能需要查看authid current_user .com/cd/E11882_01/timesten.112/e21639/sqlexamples.htm#TTPLS394"rel =" nofollow> oracle文档.也有帮助: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS682

In order to make role right applicable in stored procedures you might want to look at authid current_user in the oracle documentation. Also helpful: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS682

这篇关于使用另一个架构中的表创建存储过程会引发PLS-00201的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！