How To Truncate Table In Oracle Procedure?

In Oracle, TRUNCATE command is a DDL statement, so you can not execute it directly in Oracle Procedure. To execute DDL commands in Oracle procedure, use EXECUTE IMMEDIATE statement. The below is an example of truncating a table in Oracle procedure using Execute Immediate command.

Truncate Table in Oracle Procedure Example

In the below Oracle stored procedure, you can pass the table name as a parameter and then it will truncate that table.

CREATE OR REPLACE PROCEDURE trnct_table (i_table_name IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i_table_name;

   DBMS_OUTPUT.put_line (
      'Table ' || i_table_name || ' truncated successfully.');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Truncate table failed.');
END;

Test

SET SERVEROUTPUT ON;

BEGIN
   trnct_table ('emp');
END;
/

Output

Table emp truncated successfully.
PL/SQL procedure successfully completed.

Note: You can not ROLLBACK after truncating a table in Oracle.

See also:

• PL/SQL Program to Delete the Records from a Table