跨数据库复制数据

CREATE TABLE FND_FORM_CUSTOM_RULES_TEMP AS
SELECT * FROM FND_FORM_CUSTOM_RULES WHERE 1=2;
copy FROM roapps/welcome1234@ts51 -
Insert  FND_FORM_CUSTOM_RULES_TEMP using -
SELECT * FROM FND_FORM_CUSTOM_RULES -
WHERE FUNCTION_NAME='INV_INVTOMAI' AND SEQUENCE>15;


CREATE TABLE FND_FORM_CUSTOM_SCOPES_TEMP AS
SELECT * FROM FND_FORM_CUSTOM_SCOPES WHERE 1=2;
copy FROM roapps/welcome1234@ts51 -
Insert  FND_FORM_CUSTOM_SCOPES_TEMP using -
SELECT * FROM FND_FORM_CUSTOM_SCOPES -
WHERE RULE_ID IN(SELECT ID FROM FND_FORM_CUSTOM_RULES -
WHERE FUNCTION_NAME='INV_INVTOMAI' AND SEQUENCE>15);


CREATE TABLE FND_FORM_CUSTOM_ACTIONS_TEMP AS
SELECT * FROM FND_FORM_CUSTOM_ACTIONS WHERE 1=2;
copy FROM roapps/welcome1234@ts51 -
Insert  FND_FORM_CUSTOM_ACTIONS_TEMP using -
SELECT * FROM FND_FORM_CUSTOM_ACTIONS -
WHERE RULE_ID IN(SELECT ID FROM FND_FORM_CUSTOM_RULES -
WHERE FUNCTION_NAME='INV_INVTOMAI' AND SEQUENCE>15);
DECLARE
    g_run_date DATE:=SYSDATE;
    g_login_id   NUMBER:=-9;
    w_rules_id   NUMBER:=0;
    w_action_id  NUMBER:=0;
    w_user_id    NUMBER:=-1;
    w_cnt        NUMBER:=0;
    w_count      NUMBER:=0;
    w_temp_id    NUMBER:=0;
    CURSOR actions_temp
    IS
    SELECT ROWID FROM FND_FORM_CUSTOM_ACTIONS_TEMP ORDER BY RULE_ID,SEQUENCE;
    CURSOR rules_temp
    IS
    SELECT ROWID,ID FROM FND_FORM_CUSTOM_RULES_TEMP ORDER BY SEQUENCE;
    
BEGIN
    SELECT COUNT(*) INTO w_cnt FROM 
    FND_FORM_CUSTOM_RULES_TEMP FFCRT,
    FND_FORM_CUSTOM_RULES FFCR
    WHERE FFCRT.FUNCTION_NAME=FFCR.FUNCTION_NAME
    AND FFCRT.SEQUENCE=FFCR.SEQUENCE;
    IF(w_cnt>0)THEN
        DBMS_OUTPUT.PUT_LINE('OVERLAP ERROR!');
        RETURN;
    END IF;
    SELECT USER_ID INTO w_user_id FROM FND_USER WHERE USER_NAME='APPL_ADMIN';
--ACTION_ID更新  
    w_cnt:=0;
    SELECT COUNT(*) INTO w_cnt   FROM FND_FORM_CUSTOM_ACTIONS_TEMP;
    SELECT COUNT(*) INTO w_count FROM FND_FORM_CUSTOM_ACTIONS;
    w_count:=w_count+w_cnt;
    w_cnt:=0;
    FOR rec_actions_temp IN actions_temp LOOP
        FOR i IN 1..w_count LOOP
            w_cnt:=w_cnt+1;
            SELECT count(*) into w_temp_id from FND_FORM_CUSTOM_ACTIONS where ACTION_ID=w_cnt;
            IF(w_cnt>w_action_id AND w_temp_id=0)THEN
                w_action_id:=w_cnt;
                EXIT;
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('w_action_id:'||w_action_id);
        UPDATE FND_FORM_CUSTOM_ACTIONS_TEMP SET ACTION_ID=w_action_id
        WHERE ROWID=rec_actions_temp.ROWID;
    END LOOP;
--RULE_ID更新   
    w_cnt:=0;
    SELECT COUNT(*) INTO w_cnt   FROM FND_FORM_CUSTOM_RULES_TEMP;
    SELECT COUNT(*) INTO w_count FROM FND_FORM_CUSTOM_RULES;
    w_count:=w_count+w_cnt;
    w_cnt:=0;
    FOR rec_rules_temp IN rules_temp LOOP
        FOR i  IN 1..w_count LOOP
            w_cnt:=w_cnt+1;
            SELECT count(*) into w_temp_id from FND_FORM_CUSTOM_RULES where ID=w_cnt;
            IF(w_cnt>w_rules_id AND w_temp_id=0)THEN
                w_rules_id:=w_cnt;
                EXIT;
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('w_rules_id:'||w_rules_id);
        UPDATE FND_FORM_CUSTOM_ACTIONS_TEMP 
        SET RULE_ID=w_rules_id,
        CREATION_DATE=g_run_date,
        CREATED_BY=w_user_id,
        LAST_UPDATE_DATE=g_run_date,
        LAST_UPDATED_BY=w_user_id,
        LAST_UPDATE_LOGIN=g_login_id
        WHERE RULE_ID=rec_rules_temp.ID;
        
        UPDATE FND_FORM_CUSTOM_SCOPES_TEMP 
        SET RULE_ID=w_rules_id,
        CREATION_DATE=g_run_date,
        CREATED_BY=w_user_id,
        LAST_UPDATE_DATE=g_run_date,
        LAST_UPDATED_BY=w_user_id,
        LAST_UPDATE_LOGIN=g_login_id
        WHERE RULE_ID=rec_rules_temp.ID;
        
        UPDATE FND_FORM_CUSTOM_RULES_TEMP 
        SET ID=w_rules_id,
        CREATION_DATE=g_run_date,
        CREATED_BY=w_user_id,
        LAST_UPDATE_DATE=g_run_date,
        LAST_UPDATED_BY=w_user_id,
        LAST_UPDATE_LOGIN=g_login_id
        WHERE ROWID=rec_rules_temp.ROWID;
    END LOOP;
    
    COMMIT;
    INSERT INTO FND_FORM_CUSTOM_RULES SELECT * FROM FND_FORM_CUSTOM_RULES_TEMP; 
    INSERT INTO FND_FORM_CUSTOM_SCOPES SELECT * FROM FND_FORM_CUSTOM_SCOPES_TEMP;
    INSERT INTO FND_FORM_CUSTOM_ACTIONS SELECT * FROM FND_FORM_CUSTOM_ACTIONS_TEMP;
    COMMIT;
    SELECT COUNT(*) INTO w_cnt FROM FND_FORM_CUSTOM_RULES WHERE CREATION_DATE=g_run_date;
    DBMS_OUTPUT.PUT_LINE('FND_FORM_CUSTOM_RULES:'||w_cnt);
    SELECT COUNT(*) INTO w_cnt FROM FND_FORM_CUSTOM_SCOPES WHERE CREATION_DATE=g_run_date;
    DBMS_OUTPUT.PUT_LINE('FND_FORM_CUSTOM_SCOPES:'||w_cnt);
    SELECT COUNT(*) INTO w_cnt FROM FND_FORM_CUSTOM_ACTIONS WHERE CREATION_DATE=g_run_date;
    DBMS_OUTPUT.PUT_LINE('FND_FORM_CUSTOM_ACTIONS:'||w_cnt);
    execute immediate 'DROP TABLE FND_FORM_CUSTOM_RULES_TEMP';
    execute immediate 'DROP TABLE FND_FORM_CUSTOM_SCOPES_TEMP';
    execute immediate 'DROP TABLE FND_FORM_CUSTOM_ACTIONS_TEMP';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('SQLERRM:'||SQLERRM);
END;