Printable View
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]
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.
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.
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.
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.
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
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.