Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Access to dba views from procedure

Anthony.PFeb 22 2012 — edited Feb 23 2012
Hi all,
Using Oracle 10.2.0.5 on RedHat 64 bits, I'm encountering a strange thing while trying to make a procedure: I can access dba_tablespace view from sqlplus, but I can't from a procedure...

This one works fine: select tablespace_name from dba_tablespaces;

But this one gives me error:
CREATE OR REPLACE PROCEDURE show_ts IS
BEGIN
FOR rec IN (SELECT FROM dba_tablespaces)*
LOOP
NULL;
END LOOP;
END show_ts;

*4/14 PL/SQL: SQL Statement ignored*
*4/28 PL/SQL: ORA-00942: table or view does not exist*

My user has DBA privileges, and from session_privs I can see those:
SELECT ANY DICTIONARY
SELECT ANY TABLE

dba_tablespaces seems to be part of SYS schema, but changing my request from dba_tablespaces to sys.dba_tablespaces gives me the same error...
I also have the same error with dba_users, but again it works fine out of the procedure...

Does somebody know why?
Thanks!
This post has been answered by Nikolay Savvinov on Feb 22 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 22 2012
Added on Feb 22 2012
13 comments
6,519 views