Re: truncate table in stored pl/sql proc

Truncate table is a DDL (data definition language) command and DDL is not supported in pl/sql. You can issue the command from a pl/sql procedure using the dbms_sql package to dynamically generate the command.

Here is the code: (Note you may want to add some type of authorized table name check to this)

create or replace procedure owner.TRUNCATE_TABLE   (
   table_name varchar2,
   storage_type varchar2
  )
as
--

• procedure to allow truncation of tables owned by this procedure's
• owner by authorized users of this procedure, ie, everyone granted
• execute priviledge on this procedure has truncate authority on all
• of the procedure owner's tables.
  --
  
• This procedure accepts two parameters: the name of the table to be
• truncated and instructions on how to handle storage allowcated to
• the table.
  --
  
• Example: execute jit.truncate_table('table_name','storage_option') ;
• where storage_option = 'drop storage' or 'reuse storage'
  --
  
• mdp
  --
  
• DO NOT CREATE A PUBLIC SYNONYM FOR THIS PROCEDURE! crsor integer; rval integer; begin 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 ; /

Big Bear <ta_xiong_at_hotmail.com> wrote in article <3736EE84.2901613B_at_hotmail.com>...

> hello,
> 
> could anyone out there tell me if it is possible to include truncate
> table command in a pl/sql stored procedure.  i tried it but when i
> compile it gives error "expecting 'table' when expecting......"
> 
> thanks & regards.
>