Greg,
Try
this:
create
or replace procedure truncate_tables(in_owner in varchar2)
is
BEGIN
FOR r IN (SELECT table_name FROM dba_tables WHERE owner=in_owner)
LOOP
EXECUTE IMMEDIATE 'truncate table ' || in_owner || '.' || r.table_name;
END LOOP;
END;
/
FOR r IN (SELECT table_name FROM dba_tables WHERE owner=in_owner)
LOOP
EXECUTE IMMEDIATE 'truncate table ' || in_owner || '.' || r.table_name;
END LOOP;
END;
/
You
will need to grant select on DBA_TABLES to the owner of this procedure.
You may also need to grant ALTER ANY TABLE to the same user to allow you to
perform the Truncate function. I forget exactly which priv you need to do
this - but something extra needs to be granted to the owner of the procedure to
perform the trancate function.
Good
Luck
Tom
Mercadante
Oracle Certified Professional
Oracle Certified Professional
-----Original Message-----
From: Greg Sorrel [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 2:55 PM
To: Multiple recipients of list ORACLE-L
Subject: simple questionHi!I need to wrap this SQL into SP where I can pass new owner name. I'm planning create db table with all owners I need to pass.BEGIN
FOR r IN (SELECT table_name FROM dba_tables WHERE owner='TRAIN1')
LOOP
EXECUTE IMMEDIATE 'truncate table TRAIN1.'||r.table_name;
END LOOP;
END;
/Thanks.Greg
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
