Truncating a table in a stored procedure

Question

When I run the following in an Oracle shell it works fine

truncate table table_name

But when I try to put it in a stored procedure

CREATE OR REPLACE PROCEDURE test IS

BEGIN truncate table table_name; END test;/

it fails with

ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting:  @   ROW  or   (   or   .   or   ;   :=

Why?

Thanks, Klas Mellbourn

Answer 1

try

execute immediate 'truncate table tablename' ;

Answer 2

All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement , hence you should user;

execute immediate 'truncate table tablename' ;

Answer 3

As well as execute immediate you can also use

DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;');

The statement fails because the stored proc is executing DDL and some instances of DDL could invalidate the stored proc. By using the execute immediate or exec_ddl approaches the DDL is implemented through unparsed code.

When doing this you neeed to look out for the fact that DDL issues an implicit commit both before and after execution.