Oracle Schema名字和Object名字相同时带来的问题

最新推荐文章于 2024-05-07 14:35:09 发布

最新推荐文章于 2024-05-07 14:35:09 发布 · 146 阅读

文章标签：

本文详细解释了在Oracle数据库中，SQL和PL/SQL在解析对象名与Schema名时的不同机制，通过具体案例展示了这种机制可能导致的错误，并提供了解决方案。同时，讨论了在实际应用中遇到类似问题的解决方法，包括避免对象名与Schema名相同，以及在使用FGA功能时如何正确引用对象。

Table 名字和 Schema名字相同：

有schema和表名都为Arwen.
Schema Arwen里面有一个包PKG,包里有函数Plus,返回两数相加的结果
SELECT Arwen.PKG.Plus(1,2) FROM dual; --执行这条sql没出啥错，返回结果3.
但在一个pl/sql 块中就出错了
declare
result int;
begin
select Arwen.PKG.Plus(1,2) into result from dual;
dbms_output.put_line(result);
end;

如果把表Arwen Drop掉,则pl/sql块正确执行.(如果有函数或视图等其他对象的名字和schema相同也会出错)

关于这问题的解释

觉得有点奇怪就上网搜了下,然后在Oralce官方网站上看到有关pl/sql的名字解析方式的介绍,有这么一段话:
PL/SQL and SQL resolve qualified names differently.
For example, when resolving the table name HR.JOBS:
•PL/SQL searches first for packages, types, tables, and views named HR in the current schema, and then for objects named JOBS in the HR schema.
•SQL searches first for objects named JOBS in the HR schema, and then for packages, types, tables, and views named HR in the current schema.

按这解释貌似就可以理解上面的问题了.在sql的名字解析中,碰到带点号的这样的前缀先是把点号前面的当Schema解析,那么Arwen.PKG.Plus(1,2)这语句是先把Arwen当
Schema,然后PKG解析成Schema下的对象,在这里是包.但在PL/SQL中先把点后前面的当成当前schema下的对象,这样Arwen.PKG.Plus(1,2)中的Arwen先被解析成表Arwen,然后pkg解析成表中字段,由于没这样的字段就出错了

看起来Oracle文档上说的没错了,不过发现再弄个例子一试，还是有问题啊,文档里说HR.JOBS是个表,是表的话不太好举例,我就把JOBS改成个函数吧.示例如下
有Schema HR,HR中
有函数JOBS,(没有参数,返回字符串'jobs of schema').
有包HR,包中有函数JOBS,(没有参数,返回字符串'jobs of pkg').
如果按照文档里说的,SELECT HR.JOBS FROM dual;--应该返回的是jobs of schema.
而在pl/sql块中
declare
v_txt varchar2(100);
begin
SELECT HR.JOBS into v_txt FROM dual;
dbms_output.put_line(v_txt);--打印的应该是jobs of pkg
end;
但实际结果不管在sql中还是pl/sql中都是jobs of pkg.

难道Oracle官方文档里说的是坑爹的啊?

哎不管它坑不坑爹,平时尽量注意别把对象名和schema名搞成一样,不然到时出问题了还不容易知道哪出错了,
我其实也不是喜欢钻语法的牛角尖来研究这问题,只是在工作中确实碰到了这问题,犯晕了好久才找到这蛋痛的原因啊.
我碰到这问题是这样一种场景下,我是使用FGA这功能,创建了些审计策略来记录所有schema的DML操作.审计策略中有调用到一个包中的函数,调用时包名前加了schema做前缀.这个前缀是必须得加的.因为在其他schema中做DML操作触发审计策略调用那函数时没schema前缀会找不到的。结果由于那些审计策略所在的schema中有个表名和schema名字相同,结果悲剧就出现了.所有schema做DML操作时触发了审计策略,然后又调用那函数,但调用时出错.这意味着所有schema的DML操作都不能完成了,也意味着差不多整个数据库算是没法用了,想一想这么严重的影响就流汗了,如果要是生产环境中被我这样一整,我估计会被骂死了啊........