all_objects - weird result

[sysdba]

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]

[marist89]

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.

[sysdba]

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.

[akkerend]

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.

[sysdba]

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.

[sysdba]

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

[akkerend]

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.