| access of system table [message #8121] |
Mon, 28 July 2003 12:13  |
Jason Ru
Messages: 1
Registered: July 2003
|
Junior Member |
|
|
I log on as system and I have no problem to run:
declare
t_name varchar2(30);
cursor c_tbl is
select table_name from dba_tables where owner ='TRIM';
begin
open c_tbl;
loop
fetch c_tbl into t_name;
dbms_output.put_line('name =' || t_name);
exit when c_tbl%NOTFOUND;
end loop;
end;
But when I replace 'declare' with
create or replace procedure tbl is
I got error on the line fetch, it looks like
it can't recognize the system table dba_tables.
It wants me to declare dba_tables.
How can I correct the problem?
|
|
|
|
| Re: access of system table [message #8122 is a reply to message #8121] |
Mon, 28 July 2003 12:36  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
In PL/SQL, you must have a direct grant (not through a role) to access an object. SYSTEM sees DBA_TABLES through a role.
You really shouldn't be compiling objects in the SYSTEM schema anyway - it should be completely reserved for internal use. In any case, whatever account you end up using will need a direct grant from SYS:
SYS>grant select on dba_tables to some_user;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
