How To Truncate Table In Oracle Procedure?

How To Truncate Table In Oracle Procedure?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 1 min read

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: