Execute Immediate

[cdias]

I have a user with dba role. In this user I create a stored procedured that truncate partitions of tables
in others users.
The code is: PROCEDURE truncate_partition
(
p_username IN all_users.username%TYPE,
p_table_name IN all_tab_partitions.table_name%TYPE,
p_partition_name IN all_tab_partitions.partition_name%TYPE
)
IS
v_statement VARCHAR2(500);

BEGIN
c_log_file := util_log.fopen(c_log_filename);
util_log.put_line(c_log_file,'truncate_partition(): begin');
BEGIN
v_statement := 'ALTER TABLE ' || p_username || '.' || p_table_name ||
' TRUNCATE PARTITION ' || p_partition_name;
util_log.put_line(c_log_file, '[DEBUG] v_statement=' || v_statement);
EXECUTE IMMEDIATE v_statement;
util_log.put_line(c_log_file,'partition ' || p_partition_name ||
' from table ' || p_username || '.' || p_table_name ||
' truncated with success.');
EXCEPTION
WHEN OTHERS THEN
util_log.put_line(c_log_file, '[ERROR] Exception not hooked. SQLcode='|| SQLCODE);
util_log.put_line(c_log_file, SQLERRM);
END;
util_log.put_line(c_log_file,'truncate_partition(): end');
util_log.fclose(c_log_file);
END truncate_partition;

When I execute get from log file:
[2007-03-30 16:31:30] truncate_partition(): begin
[2007-03-30 16:31:30] [DEBUG] v_statement=ALTER TABLE COLLECTOR_MANAGER.COLLECTABLE_ATTRIBUTE_VALUE TRUNCATE PARTITION P_28
[2007-03-30 16:31:30] [ERROR] Exception not hooked. SQLcode=-942
[2007-03-30 16:31:30] ORA-00942: table or view does not exist
[2007-03-30 16:31:30] truncate_partition(): end

If I try to run only the statement works fine (in the same).
So this as nothing to do with grants.

Please help.

CDias

[davey23uk]

you need access to the tables directly, not via a role

[cdias]

But there is something that I don't understand. Why I cab execute with sucess que statement in the user with the dba role.
I also execute a grant all to the user for the table.. but it doesn't work.

[PAVB]

Why I can execute with sucess the statement in the user...

Do you mean... logged into the system as the owner of the table you want to truncate?

'cause of the owner of the objects can do whatever wants with his/hers objects