-- Filename: DBA_add_PK.sql
-- Purpose: Create a Primary key column on a table were this is not
-- yet available
-- Notes: This script will create a new script that extends your
-- tables with a extra column to store the primary and generates
-- the primary key values.
-- Date: 02-august-2007
-------------------------------------------------------------------------
DECLARE
TYPE oracle_tables IS VARRAY(20) OF VARCHAR2(30);
my_tables oracle_tables;
v_table_name VARCHAR2(60);
v_pk_name VARCHAR2(30);
v_schema VARCHAR2(30);
BEGIN
-- Put in your schema name here
v_schema := 'your_schema';
-- Put in your tables you want extend here
my_tables := oracle_tables('table01',
'table02',
'table03',
'table04',
'table05',
'table06',
'table07',
'table08',
'table09',
'table10',
'table11',
'table12',
'table13',
'table14',
'table15',
'table16',
'table17',
'table18',
'table19',
'table20');
FOR i in 1..my_tables.COUNT() LOOP
v_pk_name := SUBSTR(my_tables(i),1,1)||substr(my_tables(i),instr(my_tables(i),'_')+1,3)||'_SEQ';
v_table_name := v_schema||'.'||my_tables(i);
DBMS_OUTPUT.put_line(CHR(10)||CHR(10));
DBMS_OUTPUT.put_line('------------------------------------------------------');
DBMS_OUTPUT.put_line('-- Begin script '||v_table_name);
DBMS_OUTPUT.put_line('------------------------------------------------------');
-----------------------------------------------------------
-- Copy table structure
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('-- Copy table structure');
DBMS_OUTPUT.put_line('CREATE TABLE '||v_table_name||'_t AS SELECT * FROM '||v_table_name||' WHERE 1=0;');
-----------------------------------------------------------
-- Alter statements - add primary key column on temp table
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Alter statements - add primary key column on temp table');
DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||'_t ADD '||v_pk_name||' '||' number(10) NOT NULL;');
DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||'_t ADD CONSTRAINT pk_'||SUBSTR(my_tables(i),1,20)||'_t PRIMARY KEY ('||v_pk_name||');');
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Alter statements - add primary key field to production table');
DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD '||v_pk_name||' '||' number(10);');
-----------------------------------------------------------
-- Create Sequence
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Create Sequence');
DBMS_OUTPUT.put_line('CREATE SEQUENCE '||v_schema||'.'||'SQ_'||my_tables(i) ||' INCREMENT BY 1 START WITH 1;');
-----------------------------------------------------------
-- Create Trigger
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Create Trigger');
DBMS_OUTPUT.put_line('CREATE OR REPLACE TRIGGER '||substr(v_table_name,1,20)||'_briu_t');
DBMS_OUTPUT.put_line(' BEFORE INSERT OR UPDATE');
DBMS_OUTPUT.put_line(' ON '||v_table_name||'_t');
DBMS_OUTPUT.put_line(' REFERENCING NEW AS NEW OLD AS OLD');
DBMS_OUTPUT.put_line(' FOR EACH ROW');
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line(' BEGIN');
DBMS_OUTPUT.put_line(' -- If the PK column is empty we gonna fill this in with the next value of the sequence');
DBMS_OUTPUT.put_line(' IF :NEW.'||v_pk_name ||' IS NULL THEN');
DBMS_OUTPUT.put_line(' SELECT SQ_'||my_tables(i) ||'.NEXTVAL');
DBMS_OUTPUT.put_line(' INTO :NEW.'||v_pk_name);
DBMS_OUTPUT.put_line(' FROM DUAL;');
DBMS_OUTPUT.put_line(' END IF;');
DBMS_OUTPUT.put_line(' END;');
DBMS_OUTPUT.put_line('/');
-----------------------------------------------------------
-- Copy data from production table to the temp table
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Copy data from production table to the temp table');
DBMS_OUTPUT.put_line('INSERT INTO '||v_table_name||'_t SELECT * FROM '||v_table_name||';');
-----------------------------------------------------------
-- Truncate production table
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Truncate production table');
DBMS_OUTPUT.put_line('TRUNCATE TABLE '||v_table_name||';');
-----------------------------------------------------------
-- Modify field to production table and copy data from the temp table
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Alter statements - modify primary key field to production table');
DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD '||v_pk_name||' '||' number(10) NOT NULL;');
DBMS_OUTPUT.put_line('ALTER TABLE '||v_table_name||' ADD CONSTRAINT pk_'||SUBSTR(my_tables(i),1,20)||' PRIMARY KEY ('||v_pk_name||');');
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Copy data from temp table to the production table');
DBMS_OUTPUT.put_line('INSERT INTO '||v_table_name||' SELECT * FROM '||v_table_name||'_t ;');
-----------------------------------------------------------
-- Create trigger
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Create trigger');
DBMS_OUTPUT.put_line('CREATE OR REPLACE TRIGGER '||substr(v_table_name,1,20)||'_briu');
DBMS_OUTPUT.put_line(' BEFORE INSERT OR UPDATE');
DBMS_OUTPUT.put_line(' ON '||v_table_name);
DBMS_OUTPUT.put_line(' REFERENCING NEW AS NEW OLD AS OLD');
DBMS_OUTPUT.put_line(' FOR EACH ROW');
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line(' BEGIN');
DBMS_OUTPUT.put_line(' -- If the PK column is empty we gonna fill this in with the next value of the sequence');
DBMS_OUTPUT.put_line(' IF :NEW.'||v_pk_name ||' IS NULL THEN');
DBMS_OUTPUT.put_line(' SELECT SQ_'||my_tables(i) ||'.NEXTVAL');
DBMS_OUTPUT.put_line(' INTO :NEW.'||v_pk_name);
DBMS_OUTPUT.put_line(' FROM DUAL;');
DBMS_OUTPUT.put_line(' END IF;');
DBMS_OUTPUT.put_line(' END;');
DBMS_OUTPUT.put_line('/');
DBMS_OUTPUT.put_line('commit;');
-----------------------------------------------------------
-- Drop temp table
-----------------------------------------------------------
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line('--Drop temp table');
DBMS_OUTPUT.put_line('DROP TABLE '||v_table_name||'_t;');
DBMS_OUTPUT.put_line('------------------------------------------------------');
DBMS_OUTPUT.put_line('-- End script '||v_table_name);
DBMS_OUTPUT.put_line('------------------------------------------------------');
DBMS_OUTPUT.put_line(CHR(10));
DBMS_OUTPUT.put_line(CHR(10));
END LOOP;
END;
