CREATE OR REPLACE PROCEDURE CREATE_AUDIT_TABLE (v_table_name IN VARCHAR2, — Table Name that already exists.
v_init_extent IN NUMBER := 10240, — Initial Extent – also used for Next Extent in K
v_init_storage IN NUMBER := 10240) — Initial Table Storage in K – also used for Next Storage
IS
/*
DESCRIPTION:
This procedure creates a standard audit table based on a currently existing oracle table.
REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 19/06/2002 DRC 1. Created this procedure.
PARAMETERS:
NOTES:
Make sure owner has CREATE ANY TABLE & CREATE ANY SEQUENCE privilege.
*/
sql_string VARCHAR2(4000);
v_column_name VARCHAR2(30);
v_data_type VARCHAR2(30);
v_data_length VARCHAR2(5);
v_nullable VARCHAR2(1);
v_nullable2 VARCHAR2(30);
CURSOR get_table_info IS
select column_name, data_type, data_length, nullable
from user_tab_columns
where table_name = v_table_name
order by column_id;
v_table_info get_table_info%ROWTYPE;
BEGIN
sql_string := ‘CREATE TABLE ‘ || v_table_name ||
‘_AUDITS ( TRANSACTION_ID NUMBER NOT NULL,’;
— First of all get the NEW_ columns.
OPEN get_table_info;
<
LOOP
v_column_name := NULL;
v_data_type := NULL;
v_data_length := NULL;
v_nullable := NULL;
FETCH get_table_info INTO v_column_name, v_data_type, v_data_length, v_nullable;
IF get_table_info%NOTFOUND THEN
EXIT code_loop;
END IF;
SELECT decode(v_nullable,’N’,’NOT NULL’,’Y’,’ ‘,v_nullable) into v_nullable2 from dual;
IF v_data_length IS NULL OR v_data_type IN (‘DATE’,’NUMBER’) THEN
sql_string := sql_string || v_column_name || ‘ ‘ || v_data_type || ‘ ‘ || v_nullable2 || ‘,’ || chr(10);
ELSE
sql_string := sql_string || v_column_name || ‘ ‘ || v_data_type || ‘ (‘ || v_data_length || ‘) ‘ || v_nullable2 || ‘,’ || chr(10);
END IF;
END LOOP;
CLOSE get_table_info;
–Now for the OLD_ columns
OPEN get_table_info;
<
LOOP
v_column_name := NULL;
v_data_type := NULL;
v_data_length := NULL;
v_nullable := NULL;
FETCH get_table_info INTO v_column_name, v_data_type, v_data_length, v_nullable;
IF get_table_info%NOTFOUND THEN
EXIT code_loop2;
END IF;
SELECT decode(v_nullable,’N’,’NOT NULL’,’Y’,’ ‘,v_nullable) into v_nullable2 from dual;
IF v_data_length IS NULL OR v_data_type IN (‘DATE’,’NUMBER’) THEN
sql_string := sql_string || ‘ OLD_’ || v_column_name || ‘ ‘ || v_data_type || ‘ ‘ || v_nullable2 || ‘,’ || chr(10);
ELSE
sql_string := sql_string || ‘ OLD_’ || v_column_name || ‘ ‘ || v_data_type || ‘ (‘ || v_data_length || ‘) ‘ || v_nullable2 || ‘,’ || chr(10) ;
END IF;
END LOOP;
CLOSE get_table_info;
sql_string := sql_string || ‘TRANSACTION_TYPE VARCHAR2(10) NOT NULL, ‘ || chr(10) ||
‘TRANSACTION_DATE DATE NOT NULL, UPDATED_BY VARCHAR2(30) NOT NULL,’ || chr(10);
sql_string := sql_string || ‘ CONSTRAINT ‘ || v_table_name || ‘_AUD_PK PRIMARY KEY ( TRANSACTION_ID )’ || chr(10) ||
‘ USING INDEX TABLESPACE INDEXES PCTFREE 10’ || chr(10) ||
‘ STORAGE ( INITIAL ‘ || v_init_storage || ‘K NEXT ‘ || v_init_storage || ‘K PCTINCREASE 0 ))’ || chr(10) ||
‘ TABLESPACE DATA’ || chr(10) ||
‘ PCTFREE 10’ || chr(10) ||
‘ PCTUSED 40’ || chr(10) ||
‘ INITRANS 1’ || chr(10) ||
‘ MAXTRANS 255’ || chr(10) ||
‘ STORAGE ( ‘ || chr(10) ||
‘ INITIAL ‘ || v_init_extent || chr(10) ||
‘ NEXT ‘ || v_init_extent || chr(10) ||
‘ MINEXTENTS 1’ || chr(10) ||
‘ MAXEXTENTS 2147483645’ || chr(10) ||
‘ FREELISTS 1 FREELIST GROUPS 1 )’ || chr(10) ||
‘ NOCACHE’;
execute immediate sql_string;
dbms_output.put_line(‘Audit Table ‘ || v_table_name || ‘_AUDITS ‘ || ‘Created.’);
sql_string := NULL;
sql_string := ‘COMMENT ON TABLE ‘ || v_table_name || ‘_AUDITS IS ”This is an audit table for the ‘ || v_table_name || ‘ table”’;
execute immediate sql_string;
EXCEPTION
WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
/