Job Seekers   Employers

dbapool: Authors | Submissions | Contact Us

Forgot password? | Sign up

Home

Discussion Forum

Articles

Interview Questions

FAQs

Scripts

Rewards

Analyzer

White Papers

Blog

Certification

Downloads

Tools

Script to Create a Primary key column on a table were this is not yet available By: Babu Uppu Oct 17 2008 Script to Create a Primary key column on a table were this is not yet available   REM: Script to Create a Primary key column on a table were this is not yet available REM: REM:***************************************** REM: Author: Babu REM: Date Submitted: 19-Sept-2008 REM: REM: REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE. REM: Author will not be responsible for any damage that may be cause by this script. Purpose : Create a Primary key column on a table were this is not yet available 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;

About Us

Advertise

Terms of Use

Privacy

Newsletters

Contact Us

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads

Our Premium Sponsor