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!
Use ALL_TABLES and ALL_VIEWS instead of DBA_TABLES and DBA_VIEWS. ALL_% views should be accessible to all users.