-
Query on all_source table
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
-
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
Regards,
P.Peach
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|