all_objects - weird result
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: all_objects - weird result

  1. #1
    Join Date
    Jun 2000
    Posts
    295
    Hi,

    select count(*) from all_objects
    return 22410
    while
    select count(*) into cnt from all_objects
    within PL/SQL
    return 21274

    And if I enclosed
    create table test
    as
    select * from all_objects;

    into PL/SQL and late to do:

    select count(*) from test
    it return 22410.

    Why did I get different results?

    Thanks,

    Note: I logg into database as the same user with DBA privilege.


    [Edited by sysdba on 02-19-2001 at 02:09 PM]

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Probably has to do with your permissions via a role vs. permissions granted directly to you. Grant yourself permission on the DBA_OBJECTS table and then run your query again in both modes.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2000
    Posts
    295
    If the user does not permission on all_objects,
    I'd get Oracle error message back, which is
    not the case.

    Anyway, I did
    "grant select on dba_objects to my_dba_acct"
    from sys acct, same result.

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    If there a difference if you use PL/SQL or if you use a stored procedure?

    I axpect a difference when you use a stored procedure. Privileges for some objects have been granted to a role and not to public or to the user, and roles are disabled in stored procedures.

  5. #5
    Join Date
    Jun 2000
    Posts
    295
    I understand you said, but that seems no help:
    1. I am query all_objects only from both SQL*PLUS and
    PL/SQL
    2. Two PL/SQL gave me different rows.

  6. #6
    Join Date
    Jun 2000
    Posts
    295
    Anyone can try following two methods
    under non-sys dba account using SQL*PLUS, and you will see
    different reults:

    1.
    declare
    cnt integer;
    begin
    select count(*) into cnt from all_objects;
    dbms_output.put_line ( 'Found it? ' || cnt);
    end;
    /


    2.
    create or replace procedure test_sp
    as
    cnt integer;
    begin
    select count(*) into cnt from all_objects;
    dbms_output.put_line ( 'Found it? ' || cnt);
    end;
    /

    exec test_sp




  7. #7
    Join Date
    Jul 2000
    Posts
    296
    This is the expected behaviour. In a stored procedure roles are disabled. Objects with privileges granted to roles are in stored procedures not visible. In a PL/SQL block these object are visible.

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