UPDATE mcm SET mcm.mcm_id=(
      SELECT mcm_id FROM tmp_mcm_id
      WHERE mcm.rob_pos=tmp_mcm_id.rob_pos 
      AND mcm.rob_id=tmp_mcm_id.rob_id);

ALTER TABLE mcm_loc ADD  mcm_id NUMBER;

UPDATE mcm_loc SET mcm_loc.mcm_id=(
      SELECT mcm_id FROM tmp_mcm_id
      WHERE mcm_loc.rob_pos=tmp_mcm_id.rob_pos 
      AND mcm_loc.rob_id=tmp_mcm_id.rob_id);

ALTER TABLE mcm DROP (rob_id,rob_pos);

ALTER TABLE mcm ADD CONSTRAINT mcm_id PRIMARY KEY(mcm_id);

-- name of costraint obviously has to be globally
-- unique!
ALTER TABLE mcm_loc ADD CONSTRAINT
mcm_loc_fk1 FOREIGN KEY (mcm_id) REFERENCES mcm(mcm_id);

DROP TABLE tmp_mcm_id;

CREATE TABLE ori_loc (
      ori_id  NUMBER,
      roc_type CHAR(4),
      roc_serial NUMBER,
      side    CHAR(1),
      ori_loctime TIMESTAMP,
      FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial),
      FOREIGN KEY (ori_id) REFERENCES ori
      );

INSERT INTO ori_loc (ori_id,roc_type,roc_serial,side,ori_loctime) (SELECT ori_id,roc_type,roc_serial,side,SYSDATE FROM ori);

ALTER TABLE ori DROP (roc_type,roc_serial,side);

CREATE OR REPLACE TYPE numset_t IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION generate_series(x NUMBER,y NUMBER) 
RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN x..y LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
/



