Query on all_source table

[peach_partner]

Hi gurus,

I wrote a procedure, show_sources, that show all sources of users. The procedure is stored on user having DBA privilege. I found that the returned result from the procedure shows only sources of object granted execute to public. But if I take and run a SQL statement in the procedure via sqlplus, its result will shows all objects' source.

I would like to know why the results are different and how can I do the same returned result of sqlplus.

Thank U before hand,
P.Peach

Information
OS: Solaris SPARC 2.6
Oracle: 8.1.6
Procedure Code: CREATE OR REPLACE PROCEDURE show_sources is
cursor SHOW_ALL is
SELECT distinct owner, name
FROM all_source
WHERE owner in ('A', 'B');
BEGIN
dbms_output.enable(100000);
For rec in show_all Loop
dbms_output.put_line(rec.owner || '.' || rec.name );
End Loop;
END;
Procedure Return:
A.A2
B.B1
B.B4

SQLPlus:
sqlplus> SELECT distinct owner, name
2 FROM all_source
3 WHERE owner in ('A', 'B');

OWNER NAME
------------------------------ ------------------------------
A A1
A A2
A A3
B B1
B B2
B B3
B B4
B B5
B B6

[peach_partner]

Accepted Answer from shuminzhou feedback
Date: 08/05/2004 02:28AM PDT
Grade: A
Accepted Answer


Try adding AUTHID CURRENT_USER :

CREATE OR REPLACE PROCEDURE show_sources AUTHID CURRENT_USER is