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;
sql_stmt:='TRUNCATE TABLE ' || v_owner || '.' || p_tabname;
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 in '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/uwclassSELECT * FROM t1;