Truncating a table in a stored procedure

oracle stored-procedures plsql ddl

Klas Mellbourn · Mar 9, 2009 · Viewed 160.1k times · Source

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?

Answer

All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement. Hence you should use:

execute immediate 'truncate table schema.tablename';