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!

Not getting all of the results from ALL_OBJECTS in Procedure

429195Sep 9 2004 — edited Sep 9 2004
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 7 2004
Added on Sep 9 2004
2 comments
286 views