Sunday, April 29, 2012

Create a Primary key column on a table






-----------------------------------------------------------------------
-- 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;