Press "Enter" to skip to content

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:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *