SQL & PL/SQL

I found a great peice of code online that will allow me to have a user truncate another users
tables (see below). I know I have to grant execute and delete on the table/(s) for
the user I want to truncate the table.

In addition, for one table I need to drop ALTER one table to DROP two CONTRAINTS before
I truncate it and than add the constraints back.

Can somebody tell me what permissions I need to provide in order for tihs happen. Since this
is used for a load test and I truncate several tables. I was just going to hard-code all the
tables into the proceduree below.

If somebody has a better or cleaner approach with an example I would be greatly appreciated.



Ie

ALTER TABLE MTAS.OTAPA_PRL_LOADED DROP CONSTRAINT OTAPA_PRL_LOADED_FK1;
ALTER TABLE MTAS.OTAPA_TRAN_DTL DROP CONSTRAINT OTAPA_TRAN_DTL_FK1;


ALTER TABLE MTAS.OTAPA_PRL_LOADED ADD (
CONSTRAINT OTAPA_PRL_LOADED_FK1
FOREIGN KEY (OTAPA_SEQUENCE_NUMBER)
REFERENCES MTAS.OTAPA_TRAN_HDR (OTAPA_SEQUENCE_NUMBER));

ALTER TABLE MTAS.OTAPA_TRAN_DTL ADD (
CONSTRAINT OTAPA_TRAN_DTL_FK1
FOREIGN KEY (OTAPA_SEQUENCE_NUMBER)
REFERENCES MTAS.OTAPA_TRAN_HDR (OTAPA_SEQUENCE_NUMBER));

conn uwclass/uwclass

CREATE TABLE t1 AS
SELECT * FROM all_tables;

CREATE OR REPLACE PROCEDURE trunc_tab(p_tabname IN VARCHAR2, p_storage BOOLEAN DEFAULT FALSE) AUTHID DEFINER AS
/********************************** 
AUTHOR JP Vijaykumar
ORACLE DBA
**********************************/
 v_num    NUMBER(10):=0;
 v_owner  VARCHAR2(30);
 v_user   VARCHAR2(30);
 sql_stmt VARCHAR2(2000);
BEGIN
  SELECT username
  INTO v_owner
  FROM user_users;

  SELECT sys_context('USERENV','SESSION_USER')
  INTO v_user
  FROM DUAL;

  IF p_drop_storage THEN
      sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname || ' REUSE STORAGE';
  ELSE
      sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname;
  END IF;


  IF (v_owner = v_user) THEN
    execute immediate sql_stmt;
  ELSE
    SELECT COUNT(*)
    INTO v_num
    FROM all_tab_privs
    WHERE table_name = UPPER(p_tabname)
    AND table_schema = v_owner
    AND grantee = v_user
    AND privilege = 'DELETE';

    IF (v_num > 0) THEN
      execute immediate sql_stmt;
    ELSE
      RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges.'); 
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges');
END trunc_tab;
/

GRANT execute ON t1 TO abc;

conn abc/abc

exec uwclass.trunc_tab('T1');

conn uwclass/uwclass

GRANT delete ON t1 TO abc;

conn abc/abc

exec uwclass.trunc_tab('T1');

conn uwclass/uwclass

SELECT * FROM t1;