/**********************************************************************************
Oracle DB version : 12c
My Approach : 1. Detect the Truncate event
2. Create backup table before truncating the table
***********************************************************************************/
-- Create test table
CREATE TABLE trunc_test (ID INTEGER, NAME VARCHAR2(10));
INSERT INTO trunc_test VALUES (1,'A');
COMMIT;
-- Create trigger
CREATE OR REPLACE TRIGGER TRG_BKP_TRUNC_TABLE BEFORE DDL ON SCHEMA
-- This trigger is intended to create a backup table with data, whenever a truncate statement is issued
BEGIN
IF (ORA_SYSEVENT='TRUNCATE')
THEN
IF (ORA_DICT_OBJ_TYPE='TABLE')
-- Check if a backup table is already available. If yes, then drop the table and recreate
THEN
-- ORA_DICT_OBJ_NAME
PRC_CREATE_BKP_TABLE('TRUNC_TEST');
END IF;
END IF;
END;
-- Create Procedure
CREATE OR REPLACE PROCEDURE prc_create_bkp_table(
ip_table_name VARCHAR2) AUTHID CURRENT_USER
IS
pragma autonomous_transaction;
vc_table_name VARCHAR2(30);
vc_bkp_table_name VARCHAR2(30);
vc_query_string VARCHAR2(200);
vn_bkp_tab_exists INTEGER;
BEGIN
vc_table_name:=ip_table_name;
-- Decide the Backup table name
IF LENGTH(vc_table_name)>26 THEN
vc_bkp_table_name :=substr(vc_table_name,1,26)||'_BKP';
ELSE
vc_bkp_table_name:=vc_table_name||'_BKP';
END IF;
-- Check if the backup table already exists
vc_query_string:='SELECT COUNT(1) FROM USER_OBJECTS WHERE OBJECT_TYPE=''TABLE'' AND OBJECT_NAME='||''''||vc_bkp_table_name||'''';
EXECUTE IMMEDIATE vc_query_string INTO vn_bkp_tab_exists;
IF vn_bkp_tab_exists =1 THEN
-- If backup table already exists then drop old table and create new
vc_query_string:='DROP TABLE '||vc_bkp_table_name ||' PURGE';
EXECUTE IMMEDIATE vc_query_string;
vc_query_string:='CREATE TABLE '||vc_bkp_table_name||' AS SELECT * FROM '||vc_table_name;
EXECUTE IMMEDIATE vc_query_string;
ELSE
vc_query_string:='CREATE TABLE '||vc_bkp_table_name||' AS SELECT * FROM '||vc_table_name;
EXECUTE IMMEDIATE vc_query_string;
END IF;
END;
-- Test Functionality : TRUNCATE TABLE TRUNC_TEST;
/******************************** Error description ******************************
ORA-06512: at line 8
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
**************************************************************************************/