Re: TRUNCATE & Version 7.1.3

Oracle shipped a new plsql package with 7.1.3 that provides a way to circumvent the new restrictions on truncate. The dbms_sql package is installed during a standard install. Set serveroutput on in the glogin.sql script to see the output of this procedure.  

create or replace procedure truncate_table (

                            table_name   varchar2,
                            storage_type varchar2)

• the name of the table to truncate. It must be owned
• by the owner of this procedure. storage_type can be
• 'drop storage' or 'reuse storage'. Grant execute on
• this procedure to whoever needs truncate privleges
• on this user's tables.
• Steve Harville, Stemco Inc, 12/1/94
  --
  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 to any user or role that needs truncate grant execute on truncate_table to username, role;

  ---

• truncate_table.sql
• example : sqlplus / _at_truncate_table emp reuse storage
• Steve Harville, Stemco Inc, 12/1/94
  --
  execute <owner name goes here>.truncate_table('&1','&2 &3')

  ---

# truncate_table unix shell script
# example : truncate_table emp reuse storage
# Steve Harville, Stemco Inc, 12/1/94
 

sqlplus / _at_truncate_table $1 $2 $3  

Hope this helps.  

Steve Harville
Stemco Inc
Longview, TX
sharville_at_delphi.com Received on Fri Dec 02 1994 - 15:52:05 CET