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.
