Access to dba views from procedure
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!