Oracle : Truncate Table in Procedure

Oracle : Truncate Table in Procedure

DDL and commands like TRUNCATE are not allowed in Oracle PL/SQL blocks. However, there is a workaround. Native dynamic SQL with the EXECUTE IMMEDIATE command will do the trick. Here is an example:

create procedure MyProc
as
begin
execute immediate 'truncate table MyTable';
end;

The SQL is executed as if from a host application. Pretty much anything you can run from a SQL prompt can be run from inside a PL/SQL block this way. Because running dynamic native SQL executes a string containing SQL, PL/SQL suddenly becomes very flexible. Dynamic native SQL in Oracle allows you to combine the benefits of a host language with the benefits of stored procedures.


Here's an example of the previous procedure changed to include a parameter for the name of the table name to be truncated:

create procedure MyProc (pTable in varchar2)
as
begin
execute immediate 'truncate table ' || pTable;
end;