ABCdba.com

--
-- Title :       trunc_proc.sql
-- Description : To create procedure to
--               truncate a table of
--               different ownership without
--               having to grant the DROP ANY
--               TABLE privilege.
--
-- Usage/Notes : (1) Requires name of user or
--                   role to be given the
--                   ability to run the
--                   procedure.
--               (2) Must run as user SYS.
--               (3) Pre-requisite to trunc.sql
--                   script.
--
-- Copyright :   ABCdba.com 2005
--
 
SET VERIFY OFF
 
ACCEPT grantproc 
PROMPT 'Enter user or role to run this proc : '
 
CREATE OR REPLACE PROCEDURE trunc_proc
( table_name in varchar2 ) AS
 
c_id Integer;
 
BEGIN
c_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( c_id, 'TRUNCATE TABLE '
||table_name||' DROP STORAGE', DBMS_SQL.V7 );
DBMS_SQL.CLOSE_CURSOR( c_id );
 
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR( c_id );
RAISE;
 
END trunc_proc;
/
 
GRANT EXECUTE ON trunc_proc TO &grantproc
/
 
DROP PUBLIC SYNONYM trunc_proc
/
 
CREATE PUBLIC SYNONYM trunc_proc FOR trunc_proc
/
 
SET VERIFY ON