Data Guard today

Today had noticed that redo logs were not being applied to standby. Checking live database alert log showed errors like:

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.