Query on all_source table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query on all_source table

  1. #1
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29

    Thumbs up

    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
  •  



Click Here to Expand Forum to Full Width