Not getting all of the results from ALL_OBJECTS in Procedure
429195Sep 9 2004 — edited Sep 9 2004I created the following procedure under a DBA account. The procedure selects info from ALL_OBJECTS. When I execute the procedure under this same DBA account, It does not return objects of all owners; only owners like SYSTEM, SYS, PUBLIC, and the DBA account itself.
But if I run the same code anonymously in SQLplus, under the same DBA account, I get objects of all the other owners that I also have access to.
What do I need to do in order to have the procedure return objects of all the other owners?
Here's the procedure:
CREATE OR REPLACE procedure xxx
IS
CURSOR C1 IS
select distinct owner
from all_objects
order by owner;
OwnName VARCHAR2(30);
BEGIN
OPEN C1;
LOOP
fetch c1 into OwnName;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line ('Owner ' || OwnName);
END LOOP;
dbms_output.put_line ('Procedure xxx Ended');
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
IF C1%ISOPEN THEN CLOSE C1; END IF;
DBMS_OUTPUT.PUT_LINE (CHR(0));
DBMS_OUTPUT.PUT_LINE ('OwnName: '||OwnName);
DBMS_OUTPUT.PUT_LINE ('SQL Code: '||TO_CHAR(SQLCode));
DBMS_OUTPUT.PUT_LINE ('Error Message: '||SUBSTR(SQLERRM, 1, 200));
END;
Thank you.