A strange problem with data dic views 1

[Guest_imported]

when executing the following sql from sqlplus -

select count(table_name)
from all_tables
where owner = 'TEST1';

while logging as a user say (test3 with sysdba priv) Iam getting 2, the same sql when called from an SP its giving a value of 0. The SP is created by test3. Any idea?

 

[erre1p]

Hi,

Can you paste your SP so that I can have a look at it?

Regards. Pierre
Montreal,Quebec,Canada

 

[Guest_imported]

CREATE OR REPLACE procedure test_proc
AS
obj_name VARCHAR2(10);
var_str VARCHAR2(30);

BEGIN
for x1 in (select table_name from all_tables
where owner = 'TEST1')
loop
var_str := 'grant select on ' || x1.table_name || ' to test2';
dbms_output.put_line(var_str);
execute immediate var_str;
end loop;
end;

========================================================
The same select clause - select table_name from all_tables
where owner = 'TEST1' when run from the SQL Plus returns rows but the same in the SP return 0 rows
========================================================

 

[sem]

All_tables contains ONLY those, visible to user. Try to select from dba_tables

 

[erre1p]

Hi,

I believe test3 user needs dba privileges to access the table ALL_TABLES.

Also, have you entered the following command:

>SET SERVEROUTPUT ON

to verify that the var_str is correct? Pierre
Montreal,Quebec,Canada

 

[Guest_imported]

Yes I used the serveroutput. I actually wrote a count(*)
=======================================
select count(*) into x from all_tables
where owner = 'TEST1'
=======================================

before the loop to see the count of rows, surprisingly I get 0 but the same count from the sql prompt gives me 2 rows.

 

[erre1p]

Hi,

I have a solution to your problem:

1) SQL> connect system/manager
2) SQL> grant select on all_tables to public

===========================================================

- I have created two accounts: pierre/pierre and pierre1/pierre1

- In pierre I have created 2 tables:
TEST and TEST1

- From pierre1 I have created the following SP:

SQL> create or replace procedure test_proc
2 is
3 obj_name varchar2(10);
4 var_str varchar2(60);
5
6 BEGIN
7 FOR x1 in (select table_name from sys.all_tables
where owner = 'PIERRE')
8 LOOP
9 var_str := 'grant select on ' || x1.table_name
|| ' to test2';
10 dbms_output.put_line(var_str);
11 END LOOP;
12 END;
13 /

Procedure created.
===========================================================

- The result of test_proc is:

SQL> exec test_proc();
grant select on TEST to test2
grant select on TEST1 to test2

PL/SQL procedure successfully completed.

===========================================================

- PUBLIC is granted the right to select all_tables.
- In test_proc the line:
7 FOR x1 in (select table_name from all_tables
where owner = 'PIERRE')

all_tables has to be written sys.all_tables.

===========================================================

Hope this will help.


Pierre
Montreal,Quebec,Canada