Re: truncate table in stored pl/sql proc
From: Mark Powell <Mark.Powell_at_eds.com>
Date: 10 May 1999 15:20:35 GMT
Message-ID: <01be9af8$bd955540$a12c6394_at_J00679271.ddc.eds.com>
Date: 10 May 1999 15:20:35 GMT
Message-ID: <01be9af8$bd955540$a12c6394_at_J00679271.ddc.eds.com>
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. >Received on Mon May 10 1999 - 17:20:35 CEST