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.
Andrew
I think this is a side effect of the not well enough known fact that by default PL/SQL procedures ignore privileges granted to you via roles (rather than directly). You need to create the procedure with invoker rights. See http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#18575.
CREATE PROCEDURE create_dept (
my_deptno NUMBER,
my_dname VARCHAR2,
my_loc VARCHAR2) AUTHID CURRENT_USER AS
etc…
HTH
Regards Nigel