Oracle 9i:不存在同义词表? [英] Oracle 9i: Synonymed Table Does Not Exist?

问题描述

我创建了一个包，其中包含一个计划从单独的应用程序调用的存储过程.存储过程将返回模式中所有视图和表的排序列表.为此，它对DBA_TABLES和DBA_VIEWS同义词执行简单的选择，如下所示:

I've created a package that contains a stored procedure that I plan to invoke from a separate application. The stored procedure will return a sorted list of all the views and tables in the schema. To do that, it performs a simple select on the DBA_TABLES and DBA_VIEWS synonyms, as shown below:

CREATE OR REPLACE
PACKAGE BODY TITAN_ENTITY AS

  PROCEDURE GETSCHEMAOBJECTS (RESULTS IN OUT T_CURSOR)
  IS
    V_CURSOR T_CURSOR;
  BEGIN
    OPEN V_CURSOR FOR
       SELECT 'T' OBJECTTYPE, TABLE_NAME OBJECTNAME 
          FROM DBA_TABLES 
          WHERE OWNER = 'SONAR5'
       UNION ALL
       SELECT 'V' OBJECTTYPE, VIEW_NAME OBJECTNAME 
         FROM DBA_VIEWS 
         WHERE OWNER = 'SONAR5'
       ORDER BY OBJECTNAME;
    RESULTS := V_CURSOR;      

  END GETSCHEMAOBJECTS;

END TITAN_ENTITY;

我已经验证了相关同义词的存在，并且是公开的:

I have verified that the synonyms in question exist, and are public:

CREATE PUBLIC SYNONYM "DBA_TABLES" FOR "SYS"."DBA_TABLES"
CREATE PUBLIC SYNONYM "DBA_VIEWS" FOR "SYS"."DBA_VIEWS"

我的理解是，由于它们是公开的，因此我不需要任何其他权限即可访问它们.如果这种理解是不正确的，我希望有人会误解我的观点，并向我指出更准确的数据.

My understanding is that, because they are public, I don't need any further permissions to get to them. If that understanding is incorrect, I wish someone would disabuse me of the notion and point me to more accurate data.

现在这是我的问题:我可以在Oracle SQL Developer中打开工作表，然后从这些表中进行选择.我得到的有意义的数据就很好(事实上，有567行).但是，当我尝试执行存储过程时，Oracle抱怨编译错误，如下所示:

Now here's my problem: I can open a worksheet in Oracle SQL Developer and select from these tables just fine. I get meaningful data just fine (567 rows, as a matter of fact). But when I try to execute the stored procedure, Oracle complains with a compilation error, as shown below:

Error(9,8): PL/SQL: SQL Statement ignored
Error(10,16): PL/SQL: ORA-00942: table or view does not exist

当我双击第二条错误消息时，SQL Developer将我带到第一个FROM子句("FROM DBA_TABLES").

When I double-click on that second error message, SQL Developer takes me to the first FROM clause ("FROM DBA_TABLES").

所以我很困惑.我非常了解SQL Server，而且我是Oracle的新手，所以请多多包涵.如果您能提供一些线索，或为我指明正确的方向，我将非常感激.

So I'm fairly stumped. I know SQL Server pretty well, and I'm new to Oracle, so please bear with me. If you could provide some clues, or point me in the right direction, I'd really appreciate it.

提前谢谢！

推荐答案

使用ALL_TABLES和ALL_VIEWS代替DBA_TABLES和DBA_VIEWS.所有用户都应该可以访问ALL_％的视图.

Use ALL_TABLES and ALL_VIEWS instead of DBA_TABLES and DBA_VIEWS. ALL_% views should be accessible to all users.

这篇关于Oracle 9i:不存在同义词表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！