| ALL_OBJECTS view does not list all objects from PL/SQL procedure [message #40967] |
Thu, 14 November 2002 19:12  |
kenneth cluff
Messages: 3
Registered: November 2002
|
Junior Member |
|
|
I'm writing a stored proc to do some DDL on tables owned by a specific schema. Unfortunatly the ALL_OBJECTS view is not giving me objects owned by all schemas when I'm executing it in a stored procedure. To illustrate the problem I'll show you the different output between a select and a simple proc that should do the same thing.
1) the select
--------------
(executed in SQLPlus logged in as SYSTEM)
SQL> select unique OWNER from ALL_OBJECTS;
OWNER
------------------------------
BACKUP
DBSNMP
OUTLN
PUBLIC
RHK
RHK_CDB
RHK_CSS
RHK_INKTOMI
RHK_INT
RHK_ONLINE
RHK_USER
OWNER
------------------------------
SCOTT
SYS
SYSTEM
14 rows selected.
2. The stored procedure
-----------------------
CREATE OR REPLACE procedure LIST_OWNERS is
cursor l_cursor is select unique OWNER from all_objects ;
l_row l_cursor%rowtype;
begin
open l_cursor;
fetch l_cursor into l_row;
while l_cursor%FOUND loop
dbms_output.put_line('** OWNER='||l_row.OWNER);
fetch l_cursor into l_row;
end loop;
end LIST_OWNERS;
/
The output from sqlpplus (again logged in as SYSTEM)
SQL> set serveroutput on
SQL> call LIST_OWNERS();
** OWNER=PUBLIC
** OWNER=SYS
** OWNER=SYSTEM
Call completed.
THE QUESTION:
why am I only seeing these 3 users from the stored procedure?
Thanks in advance....
|
|
|
|
|
|
| Re: ALL_OBJECTS view does not list all objects from PL/SQL procedure [message #40999 is a reply to message #40973] |
Fri, 15 November 2002 10:01  |
kenneth cluff
Messages: 3
Registered: November 2002
|
Junior Member |
|
|
OK, that makes sense. I figured it was something to do with a different 'context' that the stored proc was executing in vs. doing a query as that user.
THe question is then how do I figure out what rights it is that give access to all the objects when doing this view, and then grant them explicitly to the user that owns the procedure (SYSTEM). Any idea what rights would control this? Any general approaches to sleuthing this out? (I guess I could just go down the list granting and ungrantig rights till I hit it - assuming it only takes one, not some combination)
|
|
|
|
| Re: ALL_OBJECTS view does not list all objects from PL/SQL procedure [message #41008 is a reply to message #40999] |
Sat, 16 November 2002 06:31 |
F. Tollenaar
Messages: 64
Registered: November 2002
|
Member |
|
|
all_tables list the tables a user has rights to see.
if you want to view all tables,use dba_tables.
If you want to create a procedure in which table X has to be visible, grant select on that table directly to the user owning the procedure.
btw, do not use user SYSTEM or SYS. They are Oracle's own users and should not be tampered with.
hth,
Frank
|
|
|
|
| Thanks and here's the PL/SQL procedure [message #41022 is a reply to message #40967] |
Mon, 18 November 2002 12:59 |
kenneth cluff
Messages: 3
Registered: November 2002
|
Junior Member |
|
|
Thanks to Frank Tollenaar for help with the visibility issues. Creating and executing the stored proc within the schema I want to affect works fine. The procedure I was trying to write was one to 'clean out' a schema for subsequent re-population via IMP (certain peculiarities of our situation made this approach necessary). The following seems to do the trick when defined in the schema you want to clean out:
CREATE OR REPLACE procedure DROP_SCHEMA_OBJECTS (
p_schema_name in varchar
) is
cursor l_table_cursor
is select * from all_objects
where OWNER=p_schema_name and OBJECT_TYPE = 'TABLE';
l_table_row l_table_cursor%rowtype;
cursor l_object_cursor
is select * from all_objects
where OWNER=p_schema_name
and OBJECT_TYPE in ('INDEX','PACKAGE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','SYNONYM')
and not OBJECT_NAME='DROP_SCHEMA_OBJECTS';
l_object_row l_object_cursor%rowtype;
begin
dbms_output.put_line('** p_schema_name='||p_schema_name);
-- first do the tables and cascade constraints
open l_table_cursor;
fetch l_table_cursor into l_table_row;
while l_table_cursor%FOUND loop
dbms_output.put_line('** table to drop='||l_table_row.OBJECT_NAME);
execute immediate 'drop table '||l_table_row.OBJECT_NAME ||' cascade constraints';
-- fetch next row
fetch l_table_cursor into l_table_row;
end loop;
close l_table_cursor;
-- now do the rest of the objects
open l_object_cursor;
fetch l_object_cursor into l_object_row;
while l_object_cursor%FOUND loop
dbms_output.put_line('** '||l_object_row.OBJECT_TYPE||' to drop='||l_object_row.OBJECT_NAME);
execute immediate 'drop '||l_object_row.OBJECT_TYPE ||' '||l_object_row.OBJECT_NAME ;
-- fetch next row
fetch l_object_cursor into l_object_row;
end loop;
close l_object_cursor;
commit;
end DROP_SCHEMA_OBJECTS;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
