Oracle 9i: Synonymed Table Does Not Exist?

Question

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.

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

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

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.

Thanks in advance!

Answer 1

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

Answer 2

If you select from a table or a view in a stored PL/SQL-procedure or a stored PL/SQL-function you need a direct grant. A grant via a database role isn't enough.

You probably need a direct grant on view dba_tables. (public) synonyms are just (public) synonyms. You need directly granted select rights.

See here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#48798240264807

Answer 3

Edit: Sorry, I glossed over the part where you seem to say that you can select from DBA_TABLES directly. Most likely the issue is that your privileges are granted through a role as someone else answered. But it's still worth explaining that your understanding of PUBLIC synonyms is incomplete, and that using ALL_TABLES would be better if it accomplishes what you need.

The synonym being PUBLIC only means that all users can reference the synonym; it does not grant them any access to the object that the synonym refers to.

What you would do to most directly solve this error is grant SELECT privilege on the SYS views to the user(s) that will run this procedure. However, I think that is a very bad idea.

As suggested by Raimonds, consider whether you can get what you need from USER_TABLES or ALL_TABLES instead. What user is calling this procedure, and what access does that user have to SONAR5's tables?

Generally, if your application is interested in a table, presumably it has some privileges on it, in which case is should be listed in ALL_TABLES.