在尝试提高系统中SP的可维护性时,我认为使用循环比硬编码一个值数组(在本例中是表名)更好,并尝试相应地重构代码,以便向系统添加或删除表不需要编辑数组。暂时抛开循环的原因不谈(我非常清楚反对它们的理由),有人能解释一下发生了什么吗?
假设有两个用户,SourceUser和DestUser都在同一个数据库中,每个用户的表都在同一个表空间中。出于报告目的,SourceUser中的大量存储过程将数据从SourceUser填充到DestUser中。作为其中的一部分,要运行的第一个过程删除DestUser中的所有表并重新创建它们。再说一次,这里不讨论这样做的相对优点。
SourceUser在DestUser上具有Drop Any Table和Create Any Table权限。在DestUser中有一个我们想要保留的表。因此,我在过程中构造的SQL如下所示:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE TABLE_NAME != 'MIDBLOG' AND OWNER = sTarget_DB) Loop
Begin
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;在本例中,sTarget_DB设置为DestUser,并且针对SourceUser运行此代码。
当该过程运行时,我发现没有表被删除(在开始之前,我确认有几十个表,其中包括一个名为MIDBLOG的表)。我在SQL Developer调试模式下运行它,执行甚至从未到达循环的内部,因为它似乎认为它没有要处理的行,但我确定select语句将返回几十个表名。
接下来,我将其修改为:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE OWNER = sTarget_DB) Loop
Begin
If T.TABLE_NAME != 'MIDBLOG' THEN
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
End If;
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;在运行这个之后,唯一被删除的表就是我不想删除的那个表!更奇怪的是,循环只执行一次,就好像select查询只返回一行一样。如果我在SQL Developer 3.2上运行debug中的过程,我可以看到它的发生。我们在SQL Developer (可能是3.1)上的同事PC上做了同样的事情,循环只执行了一次,但这一次它正确地决定不删除表MIDBLOG,并再次忽略了其他所有事情。
如果我在SQL Developer中以匿名块的形式运行上面的任何一个示例,它所做的正是我期望它所做的事情。我尝试了更详细的显式游标声明,得到了与前面相同的结果。在任何时候我都没有得到任何例外。
所有这些都是在Oracle 10g企业版10.2.0.4.0 (64位)上实现的。在Oracle11g企业版11.2.0.1.0 (64位)上一试,它就工作得很好。为什么如此基本的需求在两个版本中表现出如此巨大的差异?使用相同的代码,它可以在两个版本中按我想要的那样工作吗?
发布于 2012-11-30 03:12:47
我的猜测是问题与权限有关,而不是Oracle的版本。是否通过一个数据库中的角色和另一个数据库中的直接授权将DestUser上的权限授予SrcUser?
在运行匿名PL/SQL块之前,如果先禁用角色,会发生什么情况?
set role none;
<<run the anonymous PL/SQL block>>如果将检测添加到代码中,则对all_tables的查询是否会返回预期的表集?我的猜测是,当代码失败时,它在定义者的权限存储过程中,该过程的所有者可以通过角色访问DestUser表。由于通过角色授予的权限在定义者的权限存储过程中不可见,这将导致循环中的SELECT语句返回0行(尽管交互地运行相同的查询将返回预期的行)。另一方面,如果直接授予DestUser表上的特权,则相同的定义者权限存储过程将成功运行。并且它可以在匿名PL/SQL块中工作。
https://stackoverflow.com/questions/13631859
复制相似问题