How to do the batch operation using pl/sql

Question

I have to add primary key for lots of tables, and I got the following scripts (for one table) from stackoverflow:

ALTER TABLE table1 ADD ID NUMBER(12);
CREATE SEQUENCE table1Seq START WITH 1;
UPDATE table1 SET table1ID = table1Seq.NEXTVAL;
ALTER TABLE table1 ADD PRIMARY KEY (ID);
CREATE OR REPLACE TRIGGER table1PKSet
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  :NEW.ID := table1Seq.NEXTVAL;
END;
/

However now I have do the same operations for all the tables of the user.

So I wonder if I can use the pl/sql to loops all the tables of the user, and do the above operation?

Answer 1

I'm just going to say this is gross before I start. If you're creating scripts that automate the database creation, I would ditch the below query and just go with copy/pasting because this is so horrible it does NOT belong in your database deployment scripts.

The Query

DECLARE
    CURSOR TABLES IS SELECT * FROM USER_TABLES
                     WHERE 0 = (SELECT COUNT(*)
                                FROM USER_CONSTRAINTS
                                WHERE USER_CONSTRAINTS.TABLE_NAME = USER_TABLES.TABLE_NAME 
                                AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
                               );
BEGIN
    FOR T IN TABLES LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD ID NUMBER(12)';
        EXECUTE IMMEDIATE 'CREATE SEQUENCE '||T.TABLE_NAME||'Seq START WITH 1';
        EXECUTE IMMEDIATE 'UPDATE '||T.TABLE_NAME||' SET ID = '||T.TABLE_NAME||'Seq.NEXTVAL';
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD PRIMARY KEY (ID)';
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER '||T.TABLE_NAME||'PKSet '||CHR(10)
                          ||'BEFORE INSERT ON '||T.TABLE_NAME||' '||CHR(10)
                          ||'FOR EACH ROW '||CHR(10)
                          ||'BEGIN '||CHR(10)
                          ||':NEW.ID := '||T.TABLE_NAME||'Seq.NEXTVAL; '||CHR(10)
                          ||'END; ';
    END LOOP;
END;
/

What does this do?

Basically, it gets a list of tables and dynamically builds the SQL to do the various tasks involved. EXECUTE IMMEDIATE takes the string where we built the SQL and executes it. The CHR(10) nastiness is a newline. I wanted the whitespace in there because I don't know how leaving it out would affect Oracle's parsing. Note that in several places we concatenate the table name directly to some other text to generate a sequence or PK constraint name.

This may or may not error out if you quoted your table names during creation and are using some lower case characters. If it DOES error out, keep in mind that every statement involves a commit. An error will mean the process is half done. It also fails if the schema is not the current user. (You'll need to change USER_TABLES to ALL_TABLES and add an appropriate filter in the where clause and add the schema in front of the table name when building the SQL to make it work on another schema.)

An actual working SQLFiddle: http://sqlfiddle.com/#!4/b67fc/1 (I cannot believe this actually worked on SQLFiddle.) In this case, the query we're interested in is over in the schema definition since SQL Fiddle only allows SELECT in the query.

Good luck. You'll need it. Don't shoot yourself in the foot.