Re: truncate table in stored pl/sql proc

From: Mark Powell <>
Date: 10 May 1999 15:20:35 GMT
Message-ID: <01be9af8$bd955540$>

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

  • 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 <> wrote in article <>...

> 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.
Received on Mon May 10 1999 - 17:20:35 CEST

Original text of this message