Bug? with wrong results from all_objects in stored plsql procedures

Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used.

Run this code to see the anomaly for yourself:

set serverout on
declare
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line('all_objects: '||var1) ;
end ;
/
create or replace procedure af_temp as
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line('all_objects: '||var1) ;
end ;
/
exec af_temp ;
drop procedure af_temp ;

It’s the exact same plsql each time, but very different results reported. For example:

all_objects: 13501

PL/SQL procedure successfully completed.

Procedure created.

all_objects: 4929

PL/SQL procedure successfully completed.

Procedure dropped.

This behaviour appears to occur on all versions – I tested from 7.3 through 10gR2. The biggest discrepancies in results seem to be with DBA users other than SYS, but all users show some discrepancy.

This issue doesn’t occur with the dba_ views, so best to use them instead in stored plsql objects. That does mean explicitly granting select privileges on the dba_ views being used in stored procedures, which isn’t necessary for the all_ views.

So, is this a bug? I can’t see any mention of it all in metalink.

It’s not just an academic issue either: it caused this code of mine to fail to find and drop the objects it was meant to drop.

March 2, 2007
Posted in Bugs, Scripts

  • Thanks Nigel. I checked this and yes, “authid current user” stops the discrepancy in results. But note that the anomaly occurs (with authid definer, the default) even when the owner and the current user are one and the same.

    For example, in the below c+p I ran the code as the system user and I created the procedure as the system user. So:
    current user = SYSTEM; and
    definer = SYSTEM
    So it should not matter what authid is set to. But it does in fact – widely different results each way. Which is a bit of an unpleasant surprise.

    Andrew.

    SQL> sho user
    USER is "SYSTEM"
    SQL> set serverout on
    SQL> declare
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /
    all_objects: 14559

    PL/SQL procedure successfully completed.

    SQL> create or replace procedure af_temp authid definer as
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /

    Procedure created.

    SQL> exec af_temp ;
    all_objects: 5380

    PL/SQL procedure successfully completed.

    SQL> drop procedure af_temp ;

    Procedure dropped.

    SQL> create or replace procedure af_temp authid current_user as
    2 var1 number ;
    3 begin
    4 select count(*) into var1 from all_objects ;
    5 dbms_output.put_line('all_objects: '||var1) ;
    6 end ;
    7 /

    Procedure created.

    SQL> exec af_temp ;
    all_objects: 14560

    PL/SQL procedure successfully completed.

    SQL> drop procedure af_temp ;

    Procedure dropped.

    SQL> sho user
    USER is "SYSTEM"

  • Nigel got it right, roles are disabled (unless using invoker rights), all_objects contains only objects that a user has access to, all_objects shows you correctly only those objects you have access to. Try revoking roles from the user and the counts will match or grant all privileges granted via role directly to a user. Of course DBA users have DBA (or similar) role so the “discrepancy” seems larger, SYS has always access to all objects.
    So this is not an anomaly but a useful feature.

    • Leave a Reply

    Your email address will not be published. Required fields are marked *