This discussion is archived
6 Replies Latest reply: Feb 1, 2013 8:13 AM by user522961 RSS

grant truncate

user522961 Newbie
Currently Being Moderated
Hi,
on 11G R2,
We want to grant truncate any table to a user.
How ?
We should create the following procedure ?
1. Create the procedure to truncate the table.

create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)
as
crsor integer;
rval integer;
begin
dbms_output.put_line('Truncating Table : '|| table_name ||
' Storage : '|| storage_type);
crsor := dbms_sql.open_cursor;
dbms_sql.parse(crsor, 'truncate table '|| table_name ||
' '|| storage_type ,dbms_sql.v7);
rval := dbms_sql.execute(crsor);
dbms_sql.close_cursor(crsor);
end;
/
grant execute on <procedure_name> to <user>
Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?

Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points