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;
	<<code_loop>>
	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;
	<<code_loop2>>
	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;
/